Update Statement

Update Statement

Following are some typical update statments :

REM Simple Examples

UPDATE emp 
    SET comm = NULL 
    WHERE job = 'TRAINEE'; 

UPDATE emp 
    SET job = 'MANAGER', sal = sal + 1000, deptno = 20 
    WHERE ename = 'JONES'; 

UPDATE accounts@boston 
    SET balance = balance + 500 
    WHERE acc_no = 5001;

REM PARTITION Example

UPDATE sales PARTITION (feb96) s
   SET s.account_name = UPPER(s.account_name);

REM Complex Example

UPDATE emp a 
    SET deptno = 
        (SELECT deptno 
            FROM dept 
            WHERE loc = 'BOSTON'), 
        (sal, comm) = 
        (SELECT 1.1*AVG(sal), 1.5*AVG(comm) 
          FROM emp b 
          WHERE a.deptno = b.deptno) 
    WHERE deptno IN 
        (SELECT deptno 
          FROM dept 
          WHERE loc = 'DALLAS' 
              OR loc = 'DETROIT');

REM RETURNING Clause Example

UPDATE emp
  SET job ='MANAGER', sal = sal + 1000, deptno = 20
  WHERE ename = 'JONES'
  RETURNING sal*0.25, ename, deptno INTO bnd1, bnd2, bnd3;

REM SET VALUE Example

UPDATE table1 p SET VALUE(p) =
   (SELECT VALUE(q) FROM table2 q WHERE p.id = q.id)
   WHERE p.id = 10;

REM Correlated Update Example

UPDATE TABLE(SELECT projs 
           FROM dept d WHERE d.dno = 123)  p 
  SET p.budgets = p.budgets + 1 
  WHERE p.pno IN (123, 456);