【SQL Server】使用CTE批次更新資料表

2014-01-27

4796
0
SQL Server

【SQL Server】使用CTE批次更新資料表
最近想到一個問題，就是如果資料表的資料要做一個批次更新時候有哪些方法可以達成呢？我想這時候應該沒有人會想一個個下Update吧，況且這樣也不實際...
CTE是什麼？
一般資料表運算式 (CTE) 可視為在單一 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 陳述式之執行範圍內定義的暫存結果集。CTE 與衍生資料表類似的地方在於，它不會儲存為物件，而且其生命週期僅限於查詢期間。但是與衍生資料表不同的是，CTE 可以自我參考，而且在同一查詢中可參考多次。(TechNet)
簡單來說有點像似拋棄式的View...但是本篇重點不再CTE...就交給有興趣的朋友上網搜尋囉

最近想到一個問題，就是如果資料表的資料要做一個批次更新時候有哪些方法可以達成呢？我想這時候應該沒有人會想一個個下Update吧，況且這樣也不實際...

CTE是什麼？

一般資料表運算式 (CTE) 可視為在單一 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 陳述式之執行範圍內定義的暫存結果集。CTE 與衍生資料表類似的地方在於，它不會儲存為物件，而且其生命週期僅限於查詢期間。但是與衍生資料表不同的是，CTE 可以自我參考，而且在同一查詢中可參考多次。(TechNet)

簡單來說有點像似拋棄式的View...但是本篇重點不再CTE...就交給有興趣的朋友上網搜尋囉

以下用簡單的範例來說明

Productid	Value
A	100
B	200
C	300
D	400

但，想將商品A更新為500，商品B更新為600，此時資料筆數少，可以下兩次Update但是數量多的時候，誰會想慢慢地一直下Update指令呢？

所以小弟的想法就是，將要更新的目標值放置在其他資料表(或暫存的資料表)堆放，利用Join的方法來做大量資料更新！

將兩張資料表，先Join在一起，這樣一來要修改的資料與原始資料就有了連結，再利用Update簡單的指令來做資料更新


USE tempdb
GO
--原始資料表
CREATE TABLE table1 
(
 Productid varchar(10),
 Value int
)
--批次更新使用的資料表
CREATE TABLE table2
(
 Productid varchar(10),
 Value int
)
--填充資料
insert into table1 (Productid,Value) values ('A',100)
insert into table1 (Productid,Value) values ('B',200)
insert into table1 (Productid,Value) values ('C',300)
insert into table1 (Productid,Value) values ('D',400)
insert into table2 (Productid,Value) values ('A',500)
insert into table2 (Productid,Value) values ('B',600)
;
--將兩張資料表先做Join，存放於暫時的結果集中
--
with Batch AS
(
	SELECT table1.Productid,table1.Value,table2.Productid [Productid2],table2.Value [Value2] FROM table1
	INNER JOIN table2
	ON table1.Productid = table2.Productid
)
--下更新指令
UPDATE Batch
SET Value = Value2
--顯示最後結果
SELECT * FROM table1

--DROP TABLE TABLE1
--DROP TABLE TABLE2

大家好我是饅頭，希望大家喜歡我的文章

如果有錯誤的地方請不吝指教 ^_^

回首頁