iT邦幫忙

4

【SQL分享】SQL-Server Update資料方式(Merge,CTE..)

想舉例子並做筆記整理sql-server多種更新方式
假如大大們有其他資料庫或是更好、其他作法都可以提出討論。


舉例:

目前發現有玩家利用程式漏洞洗遊戲點數

資料表:

玩家表格

玩家ID 玩家名稱 歷史總點數 目前點數 是否停權
P001 IT邦大雄 100 50 N
P002 IT邦胖虎 9999 100 N
P003 IT邦小夫 10000 0 N

訂單

訂單ID 玩家ID 金額
0001 P001 100
0002 P002 500
0003 P003 5000
0004 P003 5000

附註:

  • 遊戲幣值是1:1

判斷方式:

遊戲歷史總點數>訂單總金額
代表該玩家作弊所以封鎖帳號


更新方式 update select from

格式:

update T
set 欄位 = 更新值
from (
    select 欄位 from 表格
) T

舉例SQL跟邏輯:

update T
set 是否停權 = 'Y'
from (
	select T2.總金額,T2.* from (
		--先整理玩家訂單總金額
		select 玩家ID,sum(金額) 總金額 from 訂單 T1
		group by 玩家ID 
	) T1
	left join 玩家表格 T2 on T1.玩家ID = T2.玩家ID
	--藉由玩家歷史總點數 > 訂單總金額,判斷出哪些是作弊玩家
	where T2.歷史總點數 > T1.總金額 
) T

Demo Link


更新方式 CTE + update

with CTE as (
    select 欄位 from 表格
)
update CTE
set 欄位 = 更新值

舉例SQL跟邏輯:

with CTE as (
	select T1.總金額,T2.* from (
		--先整理玩家訂單總金額
		select 玩家ID,sum(金額) 總金額 from 訂單 T1
		group by 玩家ID 
	) T1
	left join 玩家表格 T2 on T1.玩家ID = T2.玩家ID
	--藉由玩家歷史總點數 > 訂單總金額,判斷出哪些是作弊玩家
	where T2.歷史總點數 > T1.總金額 
)
--先取消備份在更新
--select * into 備份DB..CTE_日期 from CTE;
update CTE
set 是否停權 = 'Y'

Demo Link


更新方式 update from join

格式:

UPDATE
    T
SET
    Table_A.欄位1 = Table_B.欄位1,
    Table_A.欄位2 = Table_B.欄位2
FROM
    表格A AS T
    INNER JOIN 表格B AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.欄位3 = 'xxx'

舉例SQL跟邏輯:

update T2
set T2.是否停權 = 'Y'
from (
	--先整理玩家訂單總金額
	select 玩家ID,sum(金額) 總金額 from 訂單 T1
	group by 玩家ID 
) T1
left join 玩家表格 T2 on T1.玩家ID = T2.玩家ID
--藉由玩家歷史總點數 > 訂單總金額,判斷出哪些是作弊玩家
where T2.歷史總點數 > T1.總金額 

Demo Link

比起上兩個方式少一層查詢
但測試查詢時比較不方便、但效能好


更新方式 Merge

格式:

MERGE INTO 表格A T
   USING 表格B S 
   ON 條件篩選
WHEN MATCHED THEN
   UPDATE 
      SET 表格A.欄位 = 更新值;

舉例SQL跟邏輯:

MERGE INTO 玩家表格 T
   USING (
        select 玩家ID,sum(金額) 總金額 from 訂單 T1
        group by 玩家ID    
   ) S 
   ON T.玩家ID = S.玩家ID and T.歷史總點數 > S.總金額
WHEN MATCHED THEN
   UPDATE 
      SET 是否停權 = 'Y';

Merge除了更新外還可以新增、刪除等操作。
注意只支援2008以上版本!

DEMO LINK


小經驗

我個人習慣update select from或是CTE + update
在更新前可以方便備份資料
如:

with CTE as (
    ...
)
--先備份在更新
select * into 備份DB..CTE_日期 from CTE;
--再註解備份再更新
--update CTE
--set ....

也方便做測試查詢。


參考資料:

How do I UPDATE from a SELECT in SQL Server? - Stack Overflow


測試DDL

--測試DDL
CREATE TABLE 玩家表格(
   玩家ID  NVARCHAR(4) NOT NULL PRIMARY KEY
  ,玩家名稱  NVARCHAR(5) NOT NULL
  ,歷史總點數 INTEGER  NOT NULL
  ,目前點數  INTEGER  NOT NULL
  ,是否停權  VARCHAR(1) NOT NULL
);
INSERT INTO 玩家表格(玩家ID,玩家名稱,歷史總點數,目前點數,是否停權) VALUES ('P001',N'IT邦大雄',100,50,'N');
INSERT INTO 玩家表格(玩家ID,玩家名稱,歷史總點數,目前點數,是否停權) VALUES ('P002',N'IT邦胖虎',9999,100,'N');
INSERT INTO 玩家表格(玩家ID,玩家名稱,歷史總點數,目前點數,是否停權) VALUES ('P003',N'IT邦小夫',10000,0,'N');

CREATE TABLE 訂單(
   訂單ID NVARCHAR(4)  NOT NULL PRIMARY KEY 
  ,玩家ID NVARCHAR(4) NOT NULL
  ,金額   INTEGER  NOT NULL
);
INSERT INTO 訂單(訂單ID,玩家ID,金額) VALUES ('0001','P001',100);
INSERT INTO 訂單(訂單ID,玩家ID,金額) VALUES ('0002','P002',500);
INSERT INTO 訂單(訂單ID,玩家ID,金額) VALUES ('0003','P003',5000);
INSERT INTO 訂單(訂單ID,玩家ID,金額) VALUES ('0004','P003',5000);

1 則留言

1
神Q超人
iT邦新手 4 級 ‧ 2018-06-24 01:51:13

最後的小經驗感覺很有用!
我有時候手一滑就更新錯資料惹,
如果每次更新前都可以備份可以有效降低我手殘後造成的結果XD

純真的人 iT邦研究生 3 級‧ 2018-06-24 09:03:29 檢舉

通常以防萬一~
第一次都會先備份+先預覽查詢驗算資料是否在預期內~
然後看執行效能那個比較好@@"

暐翰 iT邦大師 9 級‧ 2018-06-24 10:57:37 檢舉

尤其有時候
使用者請你更新資料,結果後面反悔!
要把資料更新回去/images/emoticon/emoticon03.gif

這時候就發現備份資料很重要

如果我很怕的話都會先手工複製到Excel,
由其是像純真的人大大提到的是要更新運算後的結果的時候XD,
看來以後又多了一種選擇了!

我要留言

立即登入留言