Oracle Update Statements
Version 11.1
Basic Update Statements
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 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
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') 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
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;
Related Topics
Delete
Error Logging
Insert
Nested Tables
Select
Types
Contact Us ? Legal Notices and Terms of Use ? Privacy Statement
_______________ This email message and any accompanying attachments may contain information that is confidential and intended only for the use of the addressee named above. It may also be privileged. If you are not the intended recipient do not read, use, disseminate, distribute or copy or take any action in reliance on it. If you have received this message in error please notify the sender immediately and delete this message. Before opening any attachments, check them for viruses or defects. _______________
No comments:
Post a Comment