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

想举例子并做笔记整理sql-server多种更新方式
假如大大们有其他资料库或是更好、其他作法都可以提出讨论。

---

举例:

目前发现有玩家利用程式漏洞洗游戏点数

资料表:

玩家表格

玩家ID玩家名称历史总点数目前点数是否停权P001IT邦大雄10050NP002IT邦胖虎9999100NP003IT邦小夫100000N

订单

订单ID玩家ID金额0001P0011000002P0025000003P00350000004P0035000

附注:

游戏币值是1:1

判断方式:

游戏历史总点数>订单总金额
代表该玩家作弊所以封锁帐号

---

更新方式 update select from

格式:

update Tset 栏位 = 更新值from (    select 栏位 from 表格) T

举例SQL跟逻辑:

update Tset 是否停权 = 'Y'from (select T2.总金额,T2.* from (--先整理玩家订单总金额select 玩家ID,sum(金额) 总金额 from 订单 T1group by 玩家ID ) T1left join 玩家表格 T2 on T1.玩家ID = T2.玩家ID--藉由玩家历史总点数 > 订单总金额,判断出哪些是作弊玩家where T2.历史总点数 > T1.总金额 ) T

Demo Link

---

更新方式 CTE + update

with CTE as (    select 栏位 from 表格)update CTEset 栏位 = 更新值

举例SQL跟逻辑:

with CTE as (select T1.总金额,T2.* from (--先整理玩家订单总金额select 玩家ID,sum(金额) 总金额 from 订单 T1group by 玩家ID ) T1left join 玩家表格 T2 on T1.玩家ID = T2.玩家ID--藉由玩家历史总点数 > 订单总金额,判断出哪些是作弊玩家where T2.历史总点数 > T1.总金额 )--先取消备份在更新--select * into 备份DB..CTE_日期 from CTE;update CTEset 是否停权 = 'Y'

Demo Link

---

更新方式 update from join

格式:

UPDATE    TSET    Table_A.栏位1 = Table_B.栏位1,    Table_A.栏位2 = Table_B.栏位2FROM    表格A AS T    INNER JOIN 表格B AS Table_B        ON Table_A.id = Table_B.idWHERE    Table_A.栏位3 = 'xxx'

举例SQL跟逻辑:

update T2set T2.是否停权 = 'Y'from (--先整理玩家订单总金额select 玩家ID,sum(金额) 总金额 from 订单 T1group by 玩家ID ) T1left 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

--测试DDLCREATE 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);