史丹利好熱

 

為了重現問題，先建立兩個資料表

 

USE TEMPDB

-- Create table1
CREATE TABLE Table1 (Id INT, TrId INT, UpdateField VARCHAR(100))
INSERT INTO Table1 (Id, TrId, UpdateField)
SELECT 1, 11, 'who am i'
UNION ALL
SELECT 2, 12, 'who am i'
UNION ALL
SELECT 3, 13, 'who am i'
UNION ALL
SELECT 4, 14, 'who am i'
GO
-- Create table2
CREATE TABLE Table2 (Id INT, TrId INT, UpdateField VARCHAR(100))
INSERT INTO Table2 (Id, TrId, UpdateField)
SELECT 1, 21, 'David'
UNION ALL
SELECT 2, 22, 'Charlemagne'
UNION ALL
SELECT 3, 23, 'Caesar'
UNION ALL
SELECT 4, 24, 'Alexander'
GO

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

 

資料更新需求是我們希望當table 1的TrId欄位=11時，將table 2對應的欄位寫入UpdateField欄位中。

如下圖，應該要更新

1.首先執行同事寫的語法，把Update Set 換成Select

select *

FROM Table2 b,
(select * from Table1 c where c.TrId = 11) a
where a.Id = b.Id 

確實只有1筆，欄位篩選出來也是大衛王

2.執行Update Set(From Derived table)

UPDATE Table1
SET UpdateField = b.updatefield

FROM Table2 b,
(select * from Table1 c where c.TrId = 11) a
where a.Id = b.Id 

My God!4筆!這比西班牙拿歐國盃冠軍還要驚!只是少了喜!

查看table1資料，大衛王無所不在了!!!

 

3.重新建table1並塞資料(參考前置步驟)

4.幫同事把Update Table1 改成 Update a

UPDATE a
SET UpdateField = b.updatefield

FROM Table2 b,
(select * from Table1 c where c.TrId = 11) a
where a.Id = b.Id 

Bingo!正確更新了1筆!

查詢table1，內容也是正確的!

呼~後面麻煩的是整個資料表都被更新了，要救回來....

 

補充其他幾種Update from，都可以精確的更新!

--常用Update From 寫法1，這也是我們最常用的，table1有明確標示在Update及From!!!
UPDATE Table1
SET UpdateField = b.updatefield
FROM Table1 ,Table2 b
where Table1.Id = b.Id 
  and Table1.TrId = 11

--常用Update From 寫法2，為了簡化where條件，把table1 設定為a的alias，但talbe1也有明確標示在Update及From!!!
UPDATE Table1
SET UpdateField = b.updatefield
FROM Table1 a,Table2 b
where a.Id = b.Id 
  and a.TrId = 11

--常用Update From 寫法3，雖然table1也沒出現在from，但他有明確出現在where條件中。
UPDATE Table1
SET UpdateField = b.updatefield
FROM Table2 b
where b.Id = Table1.Id 
  and Table1.TrId = 11

--Update From 寫法4，很少用直接用table alias更新
UPDATE a
SET UpdateField = b.updatefield
FROM Table1 a,Table2 b
where a.Id = b.Id 
  and a.TrId = 11

 

 

參考:

UPDATE (Transact-SQL)

UPDATE from SELECT using SQL Server