當更新資料，需要從多個Table取值時，除了使用UPDATE TABLE，MERGE INTO其實也頗好用的，可以把INSERT跟UPDATE都寫在一起，讓語法更簡潔，不過，要注意的是MATCHED跟NOT MATCHED在語法中只能出現一次，所以，如果有多重更新情境的時候，就沒那麼合適了！
對開發人員的通用解法
SQL Server
UPDATE
t1
SET
t1.column = t2.column
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.id = t2.id;
Oracle (and SQL Server)
UPDATE
t1
SET
t1.colmun = t2.column
FROM
Table1 t1,
Table2 t2
WHERE
t1.ID = t2.ID;
MySQL
UPDATE
Table1 t1,
Table2 t2
SET
t1.column = t2.column
WHERE
t1.ID = t2.ID;
從多個Table取值的解法
MS SQL
UPDATE c4
SET
Price=cp.Price*p.FactorRate
FROM TableNamea_A c4
INNER JOIN TableNamea_B p
on c4.Calcid=p.calcid
INNER JOIN TableNamea_A cp
on c4.Calcid=cp.calcid
WHERE
c4..Name='MyName';
Oracle 11g
（MS SQL也適用這語法，USING的部份亦可視需要，事先生成暫存Table（＠Table或#Table），再套用在語法中）
MERGE INTO TableNamea_A u
USING
(
SELECT
c4.TableName_A_ID,(cp.Price*p.FactorRate) as CalcTot
FROM TableNamea_A c4
INNER JOIN TableNamea_B p
on c4.Calcid=p.calcid
INNER JOIN TableNamea_A cp
on c4.Calcid=cp.calcid
WHERE p.Name='MyName'
) rt
on (u.TableNamea_A_ID=rt.TableNamea_B_ID)
WHEN MATCHED THEN
UPDATE SET Price=CalcTot;
總結其他答案
只有當「匹配存在」時，才有 4 種方法可以使用另一個表中的資料來更新目標表
Query and sub-query
UPDATE si
SET
si.AccountNumber = (
SELECT
ran.AccountNumber
FROM
RetrieveAccountNumber ran
WHERE
si.LeadID = ran.LeadID
)
FROM
Sales_Import si
WHERE
EXISTS (
SELECT *
FROM RetrieveAccountNumber ran
WHERE ran.LeadID = si.LeadID
)
INNER JOIN
UPDATE si
SET
si.AccountNumber = ran.AccountNumber
FROM
Sales_Import si
INNER JOIN RetrieveAccountNumber ran
on si.LeadID = ran.LeadID
Cross JOIN
UPDATE si
SET
si.AccountNumber = ran.AccountNumber
FROM
Sales_Import si,
RetrieveAccountNumber ran
WHERE
si.LeadID = ran.LeadID
Merge
MERGE INTO Sales_Import si
USING
RetrieveAccountNumber ran
on si.LeadID = ran.LeadID
WHEN MATCHED THEN
UPDATE SET
si.accountnumber = ran.accountnumber;
從性能角度來看，帶有 JOIN-s 的變體更可取：
參考文件：
沒有留言:
張貼留言