ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Update Statements

Oracle Update Statements

原创 Linux操作系统 作者:zjh_strike 时间:2008-09-03 17:27:42 0 删除 编辑
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 (
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(
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
 
Update With Returning Clause

Returning Clause demo
UPDATE (
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
SET 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
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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8775919/viewspace-441058/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-07-22

  • 博文量
    13
  • 访问量
    14217