UPDATE Examples
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