ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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 [<schema_name>.]<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 [<schema_name>.]<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 [<schema_name>.]<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 [<schema_name>.]<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 [<schema_name>.]<table_name> <alias>
WHERE <alias.table_name> = <alias.table_name>);
conn hr/hr@pdbdev
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 [<schema_name>.]<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);
