Examples: Update

The following examples demonstrate how to replace the values of the specified columns by the values of the specified expressions for all rows of the table that satisfy the search_condition:

1. Give all employees who work for Smith a 10% raise.

   update emp
       set salary = 1.1 * salary
       where dept in
           (select dno
           from dept
           where mgr in
               (select eno
               from emp
               where ename = 'Smith'));

2. Set all salaried people who work for Smith to null.

   update emp
       set salary = null
       where dept in
           (select dno
               from dept
               where mgr in
                   (select eno
                   from emp
                   where ename = 'Smith'));

3. Update the salary of all employees having names that begin with "e," using the value for a standard raise in the table dept.

   update employee e
       from dept d
       set salary = d.std_raise * e.salary
       where e.name like 'e%' and d.dname = e.dname