SQL SERVER Update from 使用陷阱

原文: SQL SERVER Update from 使用陷阱

update A set from A left join B on

此方法常用來使用根據一個表更新另一個表的資料，來進行資料同步更新。若是A表行與B表行為一對一的對應關系，更新不存在問題，若是A表行與B表行對應關系為一對多的時候，需注意A表更新的列并非B表的累計值，而是第一個數值。

首先建立兩個表 A,B 對A表建立觸發器檢視更新資訊

CREATE TABLE yshA (
keyA VARCHAR(10),
value INT
)

CREATE TABLE yshB (
keyB VARCHAR(10),
valueB INT
)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER trg_YSHA_Up
   ON  YSHA
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @now VARCHAR(60)
    SET @now=convert(varchar,GETDATE())
    SELECT *,'i',@now FROM inserted
    SELECT *,'d',@now FROM deleted

END
GO
           

1、對資料進行更新

• 對表資料進行初始化A

INSERT INTO yshA VALUES (1,0)
INSERT INTO yshB VALUES (1,1)
INSERT INTO yshB VALUES (1,2)

SELECT * FROM  yshA A LEFT JOIN yshB B ON keyA=keyB           

執行結果

UPDATE A SET value = value + valueB FROM yshA A LEFT JOIN yshB B ON keyA=keyB

SELECT * FROM yshA           

此執行結果理論上 value 應為3 ，實際執行 value 結果為1

• 對表資料進行初始化B

DELETE FROM yshA
DELETE FROM yshB
INSERT INTO yshA VALUES (1,0)
INSERT INTO yshB VALUES (1,2)
INSERT INTO yshB VALUES (1,1)


UPDATE A SET value = value + valueB FROM yshA A LEFT JOIN yshB B ON keyA=keyB

SELECT * FROM yshA           

此方式實際執行 value 結果為2

2、 原因分析

理論上以上兩種更新的結果應為一緻，同樣的資料 方法B更新的結果與 方法A更新的結果不一緻。針對此問題進行分析，首先進行執行計劃分析，檢視其它的正常，但是left join 之後多了一步執行操作 Top 開銷 是以可以解析為什麼兩次更新的結果不一緻，因為隻根據首條關聯進行更新。