The Oracle UPDATE statement processes one or more rows in a table and sets one or more columns to the values you specify. |
Update all records | UPDATE <table_name> SET <column_name> = <value> |
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 <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>) WHERE <column_name> <condition> <value>; |
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 <table_name> <alias> SET (<column_name>,<column_name> ) = ( SELECT (<column_name>, <column_name>) FROM <table_name> WHERE <alias.column_name> = <alias.column_name>) WHERE <column_name> <condition> <value>; |
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 SELECTstatement | UPDATE (<SELECT Statement>) SET <column_name> = <value> WHERE <column_name> <condition> <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' WHERE table_name NOT LIKE '%A%';
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>); |
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 <table_name> <alias> SET (<column_name_list>) = ( SELECT <column_name_list> FROM <table_name> <alias> WHERE <alias.table_name> <condition> <alias.table_name>); |
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') ASTABLESPACE_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 (<SELECT Statement>) SET .... WHERE .... RETURNING <values_list> INTO <variables_list>; |
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 <table_name> <alias> SET VALUE (<alias>) = ( <SELECT statement>) WHERE <column_name> <condition> <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 <table_name> SET ROW = <record_name> WHERE <column_name> <condition> <value>; |
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 <column_name> <condition> <value>; |
conn sh/sh
UPDATE sales PARTITION (sales_q1_2005) s SET s.promo_id = 494 WHERE amount_sold > 9000; |