Oracle Update Statements

Version 11.1

Basic Update Statements
Update all records	UPDATE SET =
	CREATE TABLE test AS SELECT object_name, object_type FROM all_objs; SELECT DISTINCT object_name FROM test; UPDATE test SET object_name = 'OOPS'; SELECT DISTINCT object_name FROM test; ROLLBACK;
Update a specific record	UPDATE SET = WHERE =
	SELECT DISTINCT object_name FROM test; UPDATE test SET object_name = 'LOAD' WHERE object_name = 'DUAL'; COMMIT; SELECT DISTINCT object_name FROM test
Update based on a single queried value	UPDATE SET = ( SELECT FROM WHERE ) WHERE ;
	CREATE TABLE test AS SELECT table_name, CAST('' AS VARCHAR2(30)) AS lower_name FROM user_tables; desc test SELECT * FROM test WHERE table_name LIKE '%A%'; SELECT * FROM test WHERE table_name NOT LIKE '%A%'; -- this is not a good thing ... UPDATE test t SET lower_name = ( SELECT DISTINCT LOWER(table_name) FROM user_tables u WHERE u.table_name = t.table_name AND u.table_name LIKE '%A%'); -- look at the number of rows updated SELECT * FROM test; -- neither is this UPDATE test t SET lower_name = ( SELECT DISTINCT LOWER(table_name) FROM user_tables u WHERE u.table_name = t.table_name AND u.table_name NOT LIKE '%A%'); SELECT * FROM test; UPDATE test t SET lower_name = ( SELECT DISTINCT LOWER(table_name) FROM user_tables u WHERE u.table_name = t.table_name AND u.table_name LIKE '%A%') WHERE t.table_name LIKE '%A%'; SELECT * FROM test;
Update based on a query returning multiple values	UPDATE SET (, ) = ( SELECT (, ) FROM WHERE = ) WHERE ;
	CREATE TABLE test AS SELECT t. table_name, t. tablespace_name, s.extent_management FROM user_tables t, user_tablespaces s WHERE t.tablespace_name = s. tablespace_name AND 1=2; desc test SELECT * FROM test; -- does not work UPDATE test SET (table_name, tablespace_name) = ( SELECT table_name, tablespace_name FROM user_tables); -- works INSERT INTO test (table_name, tablespace_name) SELECT table_name, tablespace_name FROM user_tables; COMMIT; SELECT * FROM test WHERE table_name LIKE '%A%'; -- does not work UPDATE test t SET tablespace_name, extent_management = ( SELECT tablespace_name, extent_management FROM user_tables a, user_tablespaces u WHERE t.table_name = a.table_name AND a.tablespace_name = u.tablespace_name AND t.table_name LIKE '%A%'); -- works but look at the number of rows updated UPDATE test t SET (tablespace_name, extent_management) = ( SELECT DISTINCT u.tablespace_name, u.extent_management FROM user_tables a, user_tablespaces u WHERE t.table_name = a.table_name AND a.tablespace_name = u.tablespace_name AND t.table_name LIKE '%A%'); ROLLBACK; -- works properly UPDATE test t SET (tablespace_name, extent_management) = ( SELECT DISTINCT (u.tablespace_name, u.extent_management) FROM user_tables a, user_tablespaces u WHERE t.table_name = a.table_name AND a.tablespace_name = u.tablespace_name) WHERE t.table_name LIKE '%A%'; SELECT * FROM test;
Update the results of a SELECT statement	UPDATE (
	SELECT * FROM test WHERE table_name LIKE '%A%'; SELECT * FROM test WHERE table_name NOT LIKE '%A%'; UPDATE ( SELECT * FROM test WHERE table_name NOT LIKE '%A%') SET extent_management = 'Unknown' WHERE table_name NOT LIKE '%A%'; SELECT * FROM test;

Correlated Update
Single column	UPDATE TABLE(
	conn hr/hr CREATE TABLE empnew AS SELECT * FROM employees; UPDATE empnew SET salary = salary * 1.1; UPDATE employees t1 SET salary = ( SELECT salary FROM empnew t2 WHERE t1.employee_id = t2.employee_id); drop table empnew;
Multi-column	UPDATE SET () = ( SELECT FROM WHERE );
	CREATE TABLE t1 AS SELECT table_name, tablespace_name FROM user_tables WHERE rownum < 11; CREATE TABLE t2 AS SELECT table_name, TRANSLATE(tablespace_name,'AEIOU','VWXYZ') AS TABLESPACE_NAME FROM user_tables WHERE rownum < 11; SELECT * FROM t1; SELECT * FROM t2; UPDATE t1 t1_alias SET (table_name, tablespace_name) = ( SELECT table_name, tablespace_name FROM t2 t2_alias WHERE t1_alias.table_name = t2_alias.table_name); SELECT * FROM t1;

Nested Table Update
	See Nested Tables page

Update With Returning Clause
Returning Clause demo	UPDATE (
	conn hr/hr var bnd1 NUMBER var bnd2 VARCHAR2(30) var bnd3 NUMBER UPDATE employees SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140 WHERE last_name = 'Jones' RETURNING salary*0.25, last_name, department_id INTO :bnd1, :bnd2, :bnd3; print bnd1 print bnd2 print bnd3 rollback;
	conn hr/hr variable bnd1 NUMBER UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100 RETURNING SUM(salary) INTO :bnd1; print bnd1 rollback;

Update Object Table
Update a table object	UPDATE SET VALUE () = (
	CREATE TYPE people_typ AS OBJECT ( last_name VARCHAR2(25), department_id NUMBER(4), salary NUMBER(8,2)); / CREATE TABLE people_demo1 OF people_typ; desc people_demo1 CREATE TABLE people_demo2 OF people_typ; desc people_demo2 INSERT INTO people_demo1 VALUES (people_typ('Morgan', 10, 100000)); INSERT INTO people_demo2 VALUES (people_typ('Morgan', 10, 150000)); UPDATE people_demo1 p SET VALUE(p) = ( SELECT VALUE(q) FROM people_demo2 q WHERE p.department_id = q.department_id) WHERE p.department_id = 10; SELECT * FROM people_demo1;

Record Update
Update based on a record Note: This construct updates every column so use with care. May cause increased redo, undo, and foreign key locking issues.	UPDATE SET ROW = WHERE ;
	CREATE TABLE t AS SELECT table_name, tablespace_name FROM all_tables; SELECT DISTINCT tablespace_name FROM t; DECLARE trec t%ROWTYPE; BEGIN trec.table_name := 'DUAL'; trec.tablespace_name := 'NEW_TBSP'; UPDATE t SET ROW = trec WHERE table_name = 'DUAL'; COMMIT; END; / SELECT DISTINCT tablespace_name FROM t;

Update Partitioned Table
Update only records in a single partition	UPDATE PARTITION () SET = WHERE ;
	conn sh/sh UPDATE sales PARTITION (sales_q1_2005) s SET s.promo_id = 494 WHERE amount_sold > 9000;