Updating when Corresponding Rows Exist ::SQL Server::

Problem

You want to update rows in one table when corresponding rows exist in another. For example, if an employee appears in table EMP_BONUS, you want to increase that employee's salary (in table EMP) by 20 percent. The following result set represents the data currently in table EMP_BONUS:

 
 select empno, ename
   from emp_bonus

      EMPNO ENAME
 ---------- ---------
       7369 SMITH
       7900 JAMES
       7934 MILLER

Solution

Use a subquery in your UPDATE statement's WHERE clause to find employees in table EMP that are also in table EMP_BONUS. Your UPDATE will then act only on those rows, enabling you to increase their salary by 20 percent:

 1 update emp
 2    set sal=sal*1.20
 3  where empno in ( select empno from emp_bonus )

Discussion

The results from the subquery represent the rows that will be updated in table EMP. The IN predicate tests values of EMPNO from the EMP table to see whether they are in the list of EMPNO values returned by the subquery. When they are, the corresponding SAL values are updated.

Alternatively, you can use EXISTS instead of IN:

 update emp
    set sal = sal*1.20
  where exists ( select null
                   from emp_bonus
                  where emp.empno=emp_bonus.empno )

You may be surprised to see NULL in the SELECT list of the EXISTS subquery. Fear not, that NULL does not have an adverse effect on the update. In my opinion it increases readability as it reinforces the fact that, unlike the solution using a subquery with an IN operator, what will drive the update (i.e., which rows will be updated) will be controlled by the WHERE clause of the subquery, not the values returned as a result of the subquery's SELECT list.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table