Basic Update StatementsThe Oracle UPDATE statement processes one or more rows in a table and sets one or more columns to the values you specify.Update all recordsUPDATE <table_name>SET <column_name> = <value>CREATE TABLE test ASSELECT object_name, object_typeFROM all_objs;SELECT DISTINCT object_nameFROM test;UPDATE testSET object_name = 'OOPS';SELECT DISTINCT object_nameFROM test;ROLLBACK;Update a specific recordUPDATE <table_name>SET <column_name> = <value>WHERE <column_name> = <value>SELECT DISTINCT object_nameFROM test;UPDATE testSET object_name = 'LOAD'WHERE object_name = 'DUAL';COMMIT;SELECT DISTINCT object_nameFROM testUpdate based on a single queried valueUPDATE <table_name>SET <column_name> = ( SELECT <column_name> FROM <table_name WHERE <column_name> <condition> <value>)WHERE <column_name> <condition> <value>;CREATE TABLE test ASSELECT table_name, CAST('' AS VARCHAR2(30)) AS lower_nameFROM user_tables;desc testSELECT *FROM testWHERE table_name LIKE '%A%';SELECT *FROM testWHERE table_name NOT LIKE '%A%';-- this is not a good thing ...UPDATE test tSET 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 updatedSELECT * FROM test;-- neither is this UPDATE test tSET 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 tSET 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 valuesUPDATE <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 ASSELECT t. table_name, t. tablespace_name, s.extent_managementFROM user_tables t, user_tablespaces sWHERE t.tablespace_name = s. tablespace_nameAND 1=2;desc testSELECT * FROM test;-- does not workUPDATE testSET (table_name, tablespace_name) = ( SELECT table_name, tablespace_name FROM user_tables);-- worksINSERT INTO test(table_name, tablespace_name)SELECT table_name, tablespace_nameFROM user_tables;COMMIT;SELECT *FROM testWHERE table_name LIKE '%A%';-- does not workUPDATE test tSET 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 updatedUPDATE test tSET (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 properlyUPDATE test tSET (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 statementUPDATE (<SELECT Statement>)SET <column_name> = <value>WHERE <column_name> <condition> <value>;SELECT *FROM testWHERE table_name LIKE '%A%';SELECT *FROM testWHERE 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 UpdateSingle columnUPDATE TABLE(<SELECT STATEMENT>) <alias>SET <column_name> = ( SELECT <column_name> FROM <table_name> <alias> WHERE <alias.table_name> = <alias.table_name>);conn hr/hrCREATE TABLE empnew ASSELECT * FROM employees;UPDATE empnewSET salary = salary * 1.1;UPDATE employees t1SET salary = ( SELECT salary FROM empnew t2 WHERE t1.employee_id = t2.employee_id);drop table empnew;Multi-columnUPDATE <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 ASSELECT table_name, tablespace_nameFROM user_tablesWHERE rownum < 11;CREATE TABLE t2 ASSELECT table_name,TRANSLATE(tablespace_name,'AEIOU','VWXYZ') AS TABLESPACE_NAMEFROM user_tablesWHERE rownum < 11;SELECT * FROM t1;SELECT * FROM t2;UPDATE t1 t1_aliasSET (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 ClauseReturning Clause demoUPDATE (<SELECT Statement>)SET ....WHERE ....RETURNING <values_list>INTO <variables_list>;conn hr/hrvar bnd1 NUMBERvar bnd2 VARCHAR2(30)var bnd3 NUMBERUPDATE employeesSET job_id ='SA_MAN', salary = salary + 1000,department_id = 140WHERE last_name = 'Jones'RETURNING salary*0.25, last_name, department_idINTO :bnd1, :bnd2, :bnd3;print bnd1print bnd2print bnd3rollback;conn hr/hrvariable bnd1 NUMBERUPDATE employeesSET salary = salary * 1.1WHERE department_id = 100RETURNING SUM(salary) INTO :bnd1;print bnd1rollback; Update Object TableUpdate a table objectUPDATE <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_demo1CREATE TABLE people_demo2 OF people_typ;desc people_demo2INSERT INTO people_demo1VALUES (people_typ('Morgan', 10, 100000));INSERT INTO people_demo2VALUES (people_typ('Morgan', 10, 150000));UPDATE people_demo1 pSET 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 UpdateUpdate 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 ASSELECT table_name, tablespace_nameFROM all_tables;SELECT DISTINCT tablespace_nameFROM 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_nameFROM t; Update Partitioned TableUpdate only records in a single partitionUPDATE <table_name> PARTITION (<partition_name>)SET <column_name> = <value>WHERE <column_name> <condition> <value>;conn sh/shUPDATE sales PARTITION (sales_q1_2005) sSET s.promo_id = 494WHERE amount_sold > 9000;