傳統寫法需逐列賦值,變量中轉極其繁瑣:
-- 繁瑣寫法
DECLARE @v1 INT, @v2 VARCHAR(50), @v3 DATETIME;
SELECT @v1=c1, @v2=c2, @v3=c3 FROM Table2 WHERE id=@id;
UPDATE Table1 SET col1=@v1, col2=@v2, col3=@v3 WHERE id=@id;
UPDATE A
SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)
WHERE EXISTS (SELECT 1 FROM B WHERE A.ID = B.ID);
-- 經典聯接更新(最常用)
UPDATE A
SET A.A1 = B.B1, A.A2 = B.B2, A.A3 = B.B3
FROM A
INNER JOIN B ON A.ID = B.ID;
-- LEFT JOIN 保留 A 全量,無匹配置 NULL
UPDATE A
SET A.SellerID = B.L_TUserID
FROM T_OrderForm A
LEFT JOIN T_ProductInfo B ON B.L_ID = A.ProductID;
若 WHERE 和 SET 都需關聯同一張表,傳統寫法導致該表被掃描兩次。UPDATE ... FROM 將讀取與寫入合併為單次掃描,顯著降低邏輯讀。此外,JOIN 語法比舊式逗號連接更清晰,優化器也能生成更優計劃。
UPDATE ... FROM ... JOIN。SET col = (SELECT ...)),易導致每行執行一次子查詢,性能災難。