오라클 UPDATE 구문

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 );

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
SET (, ) = (
   SELECT (, )
   FROM
   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%');

-- 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 ()SET = ; SELECT *FROM testWHERE table_name LIKE '%A%'; SELECT *FROM testWHERE 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()
SET = (
  SELECT
  FROM
  WHERE = ;
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() SET = ; CREATE TABLE t1 ASSELECT table_name, tablespace_nameFROM user_tablesWHERE rownum < 11; CREATE TABLE t2 ASSELECT table_name,TRANSLATE(tablespace_name,'AEIOU','VWXYZ') AS TABLESPACE_NAMEFROM user_tablesWHERE rownum < 11; SELECT * FROM t1; SELECT * FROM t2; UPDATE t1 t1_aliasSET (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 ()
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 ()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_demo1VALUES (people_typ('Morgan', 10, 100000)); INSERT INTO people_demo2VALUES (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 SET ROW = ; CREATE TABLE t ASSELECT table_name, tablespace_nameFROM all_tables; SELECT DISTINCT tablespace_nameFROM 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_nameFROM t;   Update Partitioned Table Update only records in a single partition UPDATE PARTITION SET = WHERE <ｅxpression>; UPDATE sales PARTITION (sales_q1_2005) sSET s.promo_id = 494WHERE amount_sold > 9000;