Oracle Update Statements
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Object Privileges Column level update privileges can be applied to objects. Use the Object Privileges link, below, to view related code demos.
-- find column level UPDATE privileges granted or received

SELECT *
FROM dba_col_privs_made
WHERE privilege = 'UPDATE';

SELECT *
FROM dba_col_privs_recd
WHERE privilege = 'UPDATE';
 
Basic Update Statements
Update all rows UPDATE <table_name>
SET <column_name> = <value>;
conn uwclass/uwclass@pdbdev

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 filtered row or rowset 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 SELECT statement 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@pdborcl

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>);
conn uwclass/uwclass@pdbdev

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
Reference See Nested Tables: Follow link at page bottom
 
Update With Returning Clause
Returning Clause demo UPDATE (<SELECT Statement>)
SET ....
WHERE ....
RETURNING <values_list>
INTO <variables_list>;
conn hr/hr@pdborcl

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@pdborcl

variable bnd1 NUMBER

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 100
RETURNING SUM(salary) INTO :bnd1;

print bnd1

ROLLBACK;
 
Object Table Update
Update a table object UPDATE <table_name> <alias>
SET VALUE (<alias>) = (
  <SELECT statement>)
WHERE <column_name> <condition> <value>;
conn uwclass/uwclass@pdbdev

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>;
conn uwclass/uwclass@pdbdev

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 Partition
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@pdborcl

UPDATE sales PARTITION(sales_q1_2005) s
SET s.promo_id = 494
WHERE amount_sold > 9000;

Related Topics
Delete Statements
DML Statements
Error Logging
Insert Statements
Nested Tables
Object Privileges
Object Tables
Oracle Built-in Functions
Select Statements
Types

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved