Basic Update Statements
Update all records
UPDATE SET =
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 SET = WHERE =
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 SET = (
SELECT FROM WHERE )
WHERE ;
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 SET (,) = (
SELECT (,
)
FROM WHERE = )
WHERE ;
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 ()
SET = WHERE ;
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() SET = (
SELECT FROM WHERE = );
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 SET () = (
SELECT FROM WHERE );
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
See Nested Tables page
Returning Clause demo
UPDATE ()
SET ....
WHERE ....
RETURNING INTO ;
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_idINTO :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 SET VALUE () = (
)
WHERE ;
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 SET ROW = WHERE ;
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 PARTITION ()
SET = WHERE ;
conn sh/sh
UPDATE sales PARTITION
(sales_q1_2005) s
SET s.promo_id = 494
WHERE amount_sold > 9000;