Oracle连表更新

导言

业务中有连表更新的需要，自己写的SQL语句总有问题。他山之石，可以攻玉。记录一下连表更新的几种写法，也借此练习下Markdown语法！

推荐文章 →古道子

1. 普通的UPDATE

--这里要注意最后的WHERE条件。
UPDATE WB WP
   SET WP.CARDNUMBER = 
       (SELECT STOREORDNUM
          FROM WBCARDMEALTRANH W
         WHERE W.STOREORDNUM IN (SELECT CODE2 FROM PRICE_20190730)
           AND W.UUID = WP.OWNERUUID)
WHERE EXISTS(SELECT 1 FROM WBCARDMEALTRANH W
         WHERE W.STOREORDNUM IN (SELECT CODE2 FROM PRICE_20190730)
           AND W.UUID = WP.OWNERUUID);

2. PL/SQL之FOR UPDATE

--需要注意，在SELECT中要有被更新表的rowid。要不然Oracle不知道你要更新哪个表，嘿嘿。
SELECT W.STOREORDNUM, WP.*, WP.ROWID
  FROM WBCARDMEALTRANH W, WBCARDMEALTRANPAYMENT WP
 WHERE W.UUID = WP.OWNERUUID
   AND W.STOREORDNUM = '00010001C180106160746'
   FOR UPDATE;

不知道为啥，这样for update之后有的字段还是“只读”状态；而只for update一张表就没事...

3. 内联视图更新

--这种方法很聪明啊！咱们要学习这种内联视图构建表的模式。
UPDATE (SELECT W.STOREORDNUM S, WP.CARDNUMBER C, WP.NAME NAME, WP.CODE CODE
          FROM WBCARDMEALTRANH W, WBCARDMEALTRANPAYMENT WP
         WHERE W.UUID = WP.OWNERUUID
           AND W.STOREORDNUM IN (SELECT CODE2 FROM PRICE_20190730)) T1
   SET T1.C = T1.S

4. merge更新

--我也是第一次用merge，好不好用另说，人家真的能连表更新;不要强行翻译英文了，有点拗口。。
merge into WBCARDMEALTRANPAYMENT T1
using (select UUID, STOREORDNUM
         from WBCARDMEALTRANH
        WHERE STOREORDNUM IN (SELECT CODE2 FROM PRICE_20190730)) t
on (t.UUID = t1.OWNERUUID)
when matched then
  update set t1.CARDNUMBER = t.STOREORDNUM
when not matched then
  insert
  VALUES
    ('8ad4c68e606f111d01606f186c5f002F',
     '快钱扫码',
     '8ad4c68e606f111d01606f176c110027',
     '1',
     '15.0000',
     '8ad4c68e606f111d01606f182fbd0029',
     '',
     '',
     2);