| 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>); |
CREATE TABLE test AS
SELECT table_name
FROM all_tables;
ALTER TABLE test
ADD (lower_name VARCHAR2(30));
SELECT *
FROM test
WHERE table_name LIKE '%A%';
UPDATE test t
SET lower_name = (
SELECT DISTINCT lower(table_name)
FROM all_tables a
WHERE a.table_name = t.table_name
AND a.table_name LIKE '%A%');
COMMIT;
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>);
|
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%');
-- does not work
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
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%';
COMMIT;
SELECT *
FROM test; |
Update the results of a SELECT statement |
UPDATE (<SELECT Statement>)
SET <column_name> = <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';
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 empbak AS
SELECT * FROM employees;
UPDATE employees
SET salary = salary * 1.1;
COMMIT;
UPDATE employees t1
SET salary = (
SELECT salary
FROM empbak t2
WHERE t1.employee_id = t2.employee_id); |
Multi-column |
UPDATE TABLE(<SELECT STATEMENT>)
<alias>
SET <column_name> = <value>; |
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 ....; |
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 |
conn hr/hr
variable bnd1 NUMBER
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 100
RETURNING SUM(salary) INTO :bnd1;
print bnd1 |
| |
| Update Object Table |
Update a table object |
UPDATE (<SELECT Statement>)
SET ....; |
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; |
| |
| Record Update |
Update based on a record |
UPDATE <table_name>
SET ROW = <record_name>; |
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 <expression>; |
UPDATE sales PARTITION
(sales_q1_2005) s
SET s.promo_id = 494
WHERE amount_sold > 9000; |