티스토리 뷰
출처 : http://www.psoug.org/reference/update.html
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
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; |
'Dev > Oracle' 카테고리의 다른 글
[펌] oracle table 관련 구문 (0) | 2007.10.26 |
---|---|
[펌] oracle tablespace 관련 구문 (0) | 2007.10.11 |
[펌] oracle export 관련 구문 (0) | 2007.09.02 |
[펌] Logging Localized Message [메시지 로깅] (0) | 2007.08.22 |
[펌] oracle : connect by ~ start with ~ (0) | 2007.01.03 |
댓글
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
TAG
- 파워콤해지
- 금연
- GB-P100
- 바이크
- VMware
- 헌혈
- 안드로이드 앱
- 짜증
- 낭패
- 오라클
- Git
- 충동구매
- 출근
- Java
- 출근길
- 무료 프로그램
- 김용
- 액땜
- ubuntu
- 최대 메모리
- connect by
- 일하는 곳
- oracle
- Eclipse
- 윈도우
- 프로젝트
- 정부과천청사
- iBATIS
- 법무부
- EditPlus
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
글 보관함