oracle update command

	Oracle Update Statements
	Version 10.1

Basic Update Statements
Update all records	UPDATE <table_name> SET <column_name> = <value>
	CREATE TABLE test AS SELECT object_name, object_type FROM all_objects; SELECT DISTINCT object_name FROM test; UPDATE test SET object_name = 'OOPS'; SELECT DISTINCT object_name FROM test; ROLLBACK;
Update a specific record	UPDATE <table_name> SET <column_name> = <value> WHERE <column_name> = <value>
	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 <table_name> SET <column_name> = (   SELECT <column_name>   FROM <table_name   WHERE <column_name> <condition> <value>);
	CREATE TABLE test AS SELECT table_name FROM all_tables; ALTER TABLE test ADD (lower_name VARCHAR2(30)); SELECT * FROM test WHERE table_name LIKE '%A%'; UPDATE test t SET lower_name = (  SELECT DISTINCT lower(table_name)  FROM all_tables a  WHERE a.table_name = t.table_name  AND a.table_name LIKE '%A%'); COMMIT; SELECT * FROM test;
Update based on a query returning multiple values	UPDATE <table_name> <alias> SET (<column_name>,<column_name> ) = (    SELECT (<column_name>, <column_name>)    FROM <table_name>    WHERE <alias.column_name> = <alias.column_name>);
	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%'); -- does not works 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 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%'; COMMIT; SELECT * FROM test;
Update the results of a SELECT statement	UPDATE (<SELECT Statement>) SET <column_name> = <value>;
	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'; SELECT * FROM test;
Correlated Update
Single column	UPDATE TABLE(<SELECT STATEMENT>) <alias> SET <column_name> = (   SELECT <column_name>   FROM <table_name> <alias>   WHERE <alias.table_name> = <alias.table_name>;
	UPDATE table1 t_alias1  SET column = (   SELECT expr    FROM table2 t_alias2    WHERE t_alias1.column = t_alias2.column);
Multi-column	UPDATE TABLE(<SELECT STATEMENT>) <alias> SET <column_name> = <value>;
	UPDATE table1 t_alias1  SET (col1, col2) = (   SELECT col1, col2   FROM table2 t_alias2   WHERE t_alias1.col3 = t_alias2.col3);
Nested Table Update
	See Nested Tables page
Update With Returning Clause
Returning Clause demo	UPDATE (<SELECT Statement>) SET ....;
	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
	conn hr/hr variable bnd1 NUMBER UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100 RETURNING SUM(salary) INTO :bnd1; print bnd1
Update Object Table
Update a table object	UPDATE (<SELECT Statement>) SET ....;
	UPDATE table1 p SET VALUE(p) = (SELECT VALUE(q) FROM table2 q WHERE p.id = q.id) WHERE p.id = 10;
Record Update
Update based on a record	UPDATE <table_name> SET ROW = <record_name>;
	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 <table_name> PARTITION <partition_name> SET <column_name> = <value> WHERE <expression>;
	UPDATE sales PARTITION (sales_q1_2005) s SET s.promo_id = 494 WHERE amount_sold > 9000;

Related Topics

Delete

Insert

Nested Tables

Select

Types