[转载]Oracle Update Statements Version 10.2

Basic Update Statements
Update all records UPDATE <table_name>
SET <column_name> = <value>
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 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 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
 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>;
UPDATE table1 t_alias1
SET (col1, col2) = (
  SELECT col1, col2
  FROM table2 t_alias2
  WHERE t_alias1.col3 = t_alias2.col3);
 
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 ....;
UPDATE table1 p SET VALUE(p) =
(SELECT VALUE(q) FROM table2 q WHERE p.id = q.id)
WHERE p.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;

posted on 2006-11-03 14:00  today  阅读(844)  评论(0编辑  收藏  举报

导航

09夏旅游鞋代购专柜正品打折李宁/LINING男跑鞋休闲球鞋