update關聯其他表批量更新資料

批量更新表時，update一個表的列時，需要依賴另外的表，這種依賴可以是where條件子句，也可以要update的field的值依賴另外的表

   通常有兩種做法

   1.使用存儲過程

   2.在程式代碼裡逐條循環執行

這裡給出一種更高效、簡潔的做法，批量更新SQL ，一句SQL就可以替代麻煩的循環過程，有MS SQLServer、Oracle、DB2下的寫法

--關鍵點：t4和t1是同一個table，primary key肯定也是同一個，

--并以它進行關聯，這樣在 select語句裡即可引用到要update的表的fields 

UPDATE Table1 AS t1 

SET (Field1,Field2) = (SELECT Field21, Field22 

                       FROM Table2 t2 

                            INNER JOIN Table3 t3 

                                 ON t3.Field31 = t2.Field23 

                            INNER JOIN Table4 t4 

                                 ON t3.Field32 = t4.Filed41 

                       WHERE t2.Field24 >= '' 

                                      AND t1.fId = t4.fId); 

----------------------------MS SQLServer --------------------------------------

UPDATE t1 

SET Field1 = Field21, Field2 = Field22 

FROM Table2 t2 

         INNER JOIN Table3 t3 

              ON t3.Field31 = t2.Field23 

         INNER JOIN Table4 t4 

              ON t3.Field32 = t4.Filed41 

WHERE ((t2.Field24 >= '') 

                  AND t1.fId = t4.fId); 

----------------------------Oracle --------------------------------------------

UPDATE Table1 t1 

SET (Field1,Field2) = (SELECT Field21, Field22 

                       FROM Table2 t2 

                            INNER JOIN Table3 t3 

                                 ON t3.Field31 = t2.Field23 

                            INNER JOIN Table4 t4 

                                 ON t3.Field32 = t4.Filed41 

                       WHERE ((t2.Field24 >= '') 

                                 AND t1.fId = t4.fId)) 

WHERE EXISTS (SELECT Field21, Field22 

              FROM Table2 t2 

                   INNER JOIN Table3 t3 

                        ON t3.Field31 = t2.Field23 

                   INNER JOIN Table4 t4 

                        ON t3.Field32 = t4.Filed41 

              WHERE ((t2.Field24 >= '') 

                               AND t1.fId = t4.fId)); 

---------------------------------DB2 ------------------------------------------

UPDATE Table1 AS t1 

SET (Field1,Field2) = (SELECT Field21, Field22 

                       FROM Table2 t2 

                            INNER JOIN Table3 t3 

                                 ON t3.Field31 = t2.Field23 

                            INNER JOIN Table4 t4 

                                 ON t3.Field32 = t4.Filed41 

                       WHERE ((t2.Field24 >= '') 

                                         AND t1.fId = t4.fId)) 

WHERE EXISTS (SELECT Field21, Field22 

              FROM Table2 t2 

                   INNER JOIN Table3 t3 

                        ON t3.Field31 = t2.Field23 

                   INNER JOIN Table4 t4 

                        ON t3.Field32 = t4.Filed41 

              WHERE ((t2.Field24 >= '') 

                                AND t1.fId = t4.fId));

-----轉載自部落格園

轉載于:https://www.cnblogs.com/AppleZhang/p/4311741.html