認識oracle的update更新

舟之橋發表於2013-01-30
Oracle

轉發地址：http://blog.csdn.net/zqpgood/article/details/6065495


這兩天給新同事安排了一個工作，即做一個update 的級聯更新，在實際操作中發現了一個問題。就是對於oracle的更新的語法，大部分人尤其是學過SqlServer的人在使用oracle的時候對於oracle的更新會有許多的疑問。就此記下，以便日後查閱

 update a set a.col1 = (select b.col1  from b  where b.col2 = a.col2)
 where exists
 (select * from b where a.col2 = b.col2)

/**oracle與sqlserver更新update的用法有不同，oracle是怎麼處理的呢
update a set a.col1=100     將所有行全部的特定列col1更新為特定值
update a set a.col1=100 where a.col2<10 將滿足col2條件的行的col1列的值更新為特定的值
update a set a.col1=a.col1+a.col2 where a.col2<10 同一個表中的簡單計算更新
update a set a.col1=(select b.col1 from b where a.col2=b.col2)
where exists(select * from b where a.col2=b.col2) 級聯更新,將滿足a.col2=b.col2的行的a.col1更新為對應的
b.col1的值。當且僅當a=b時可以將where條件去掉。這個更新還可以這樣理解：
update a set a.col1=(select b.col1 from b where a.col2=b.col2)表示對於a中所有行滿足a.col2=b.col2
的進行更新，不滿足條件的也更新，只不過找不到對應的值，只能將空值賦之，如果此時a.col1不允許為空那麼會報插入空值錯誤。
所以只有加上where條件，才能將a.col2<>b.col2的那些在a中的資料得以倖存（不被更新為空）。

oracle的INSERT、UPDATE、MERGE

ORACLE 2009-12-21 08:40:55 閱讀68 評論0   字號：大中小 訂閱

/*用COURSE表為模板，建立一張新表COURSE_NEW，並且包括COURSE表的所有記錄 */

CREATE TABLE COURSE_NEW AS SELECT * FROM COURSE;

/*清空COURSE_NEW表中的所有記錄*/
TRUNCATE TABLE COURSE_NEW;

 

/*往COURSE_NEW表中增加以下記錄：
NO COURSE_NAME
A001 ORACLE資料庫管理
A002 SQLSERVER安全指南
A003 Hibernate全攻略
A004 .NET
*/
INSERT INTO COURSE_NEW(NO,COURSE_NAME)
SELECT 'A001','ORACLE資料庫管理' FROM DUAL
UNION
SELECT 'A002','SQLSERVER安全指南' FROM DUAL
UNION
SELECT 'A003','Hibernate全攻略'  FROM DUAL
UNION 
SELECT 'A004','.NET' FROM DUAL;
COMMIT;


/*根據COURSE表中的NO欄位，用COURSE_NEW更新COURSE表*/

UPDATE  (SELECT /*+ BYPASS_UJVC */ A.NO,A.COURSE_NAME,B.NO AS BNO,B.COURSE_NAME AS BNAME
               FROM COURSE A,COURSE_NEW B
               WHERE A.NO=B.NO)
SET NO=BNO,COURSE_NAME=BNAME
COMMIT;


/*分別使用INSERT/UPDATE和MERGE命令實現，用COURSE_NEW更新COURSE表中的記錄，如果存在，則更新，不存在則INSERT*/
--用MERGE實現如下：

INSERT INTO COURSE_NEW(NO,COURSE_NAME) --為了便於操作，先在COURSE_NEW中插入一條記錄
SELECT 'A005','HCNE網路工程師' FROM DUAL;
COMMIT;
MERGE INTO COURSE A
USING COURSE_NEW B ON(A.NO=B.NO)
WHEN MATCHED THEN 
UPDATE SET A.COURSE_NAME=B.COURSE_NAME
WHEN NOT MATCHED THEN 
INSERT(A.NO,A.COURSE_NAME)
VALUES(B.NO,B.COURSE_NAME);
COMMIT;

--用INSERT/UPDATE實現如下

INSERT INTO COURSE_NEW(NO,COURSE_NAME)--為了便於操作，再在COURSE_NEW中插入一條記錄
SELECT 'A006','CCNA網路工程師' FROM DUAL;
COMMIT;

--利用UPDATE對於編號相同的欄位進行更新
UPDATE COURSE A SET(NO,COURSE_NAME)=
       (SELECT B.NO,B.COURSE_NAME 
       FROM COURSE_NEW B
       WHERE A.NO=B.NO )
WHERE  EXISTS
       (SELECT 1 FROM COURSE_NEW B
            WHERE A.NO=B.NO);
COMMIT;
--利用INSERT對於原表中沒有的進行新增
INSERT INTO COURSE
SELECT * FROM COURSE_NEW A
        WHERE NOT EXISTS(SELECT 1 FROM COURSE B WHERE A.NO=B.NO);
COMMIT;

 


/*用一組語句代替對實現對全表的更新操作*/
--先用COURSE_TEST記錄COURSE表的狀態，以便在刪除COURSE之後記錄相關欄位
CREATE TABLE COURSE_TEST AS SELECT * FROM   COURSE  WHERE ROWNUM<1;
INSERT /*+ APPEND */ INTO COURSE_TEST 
SELECT * FROM COURSE WHERE ROWNUM<5;
COMMIT;
--刪除COURSE表
TRUNCATE TABLE COURSE;
--更新COURSE表
INSERT /*+APPEND*/ INTO COURSE 
SELECT A.NO,A.COURSE_NAME FROM COURSE_NEW A,COURSE_TEST B WHERE A.NO=B.NO;
COMMIT;

相關文章