SQL UPDATE 關聯查詢寫法大全：標準語法、跨數據庫差異、JOIN 更新與集合賦值模式

標準單表更新

UPDATE TableA SET Col1 = Val1, Col2 = Val2 WHERE Condition;

從 SELECT 結果集賦值：集合賦值語法（Oracle/DB2/PostgreSQL）

UPDATE TableB
SET (Col1, Col2, Col3) = (SELECT Val1, Val2, Val3 FROM TableA WHERE TableA.Id = TableB.Id)
WHERE EXISTS (SELECT 1 FROM TableA WHERE TableA.Id = TableB.Id);

⚠️ SQL Server 不支持此語法，會報錯。

SQL Server / Sybase 專用：UPDATE ... FROM ... JOIN

-- 隱式連接 (舊風格)
UPDATE B SET B.ClientName = A.Name FROM TableA A, TableB B WHERE A.Id = B.Id;

-- 顯式 JOIN (推薦，可 LEFT/RIGHT/INNER)
UPDATE B
SET B.ClientName = A.Name
FROM TableB B
LEFT JOIN TableA A ON A.Id = B.Id;

-- 多表更新同步
UPDATE A
SET A.Col1 = B.Col1, A.Col2 = B.Col2
FROM TableA A
INNER JOIN TableB B ON A.Id = B.Id
WHERE A.Status = 1;

優勢：單次掃描源表，避免 WHERE 和 SET 子查詢各掃描一次，性能顯著優於子查詢寫法。

Oracle/DB2 兼容寫法

UPDATE TableB
SET (Col1, Col2) = (SELECT Val1, Val2 FROM TableA WHERE TableA.Id = TableB.Id)
WHERE Id IN (SELECT Id FROM TableA WHERE TableA.Id = TableB.Id);

跨數據庫更新（Linked Server）

UPDATE LocalDB.dbo.TableB
SET Col1 = Remote.Col1
FROM LocalDB.dbo.TableB B
INNER JOIN [LinkedSvr].RemoteDB.dbo.TableA A ON A.Id = B.Id;

UPDATE 關聯 DELETE 同理

DELETE B FROM TableB B
LEFT JOIN TableA A ON A.Id = B.Id
WHERE A.Id IS NULL; -- 刪除 B 中無對應 A 的行

最佳實踐總結