Sqlserver update\delete用inner join關聯，會update\delete關鍵字後面的表關聯到的行

lusklusklusk發表於2022-10-18

SQLServerdelete

結論
1、UPDATE A SET A.id=B.id FROM tableA A INNER JOIN tableB B ON A.ID = B.ID語句只更新updae關鍵後面的tableA表，不會更新tableB表  
2、DELETE A FROM tableA A INNER JOIN tableB B ON A.ID = B.ID語句只刪除delete關鍵後面的tableA表，不會刪除tableB表
3、不管UPDATE還是DELETE，INNER JOIN關聯多少行，就UPDATE\DELETE多少行

需求
1、源表algocndbalias.CNDB.dbo.cn_trademap的股票程式碼資料會新增也會被修改
2、把源表資料同步到目標表dbo.test1
3、同步過程中把目標表和源表不一致的，把目標表的資料改成源表的資料
4、同步過程中把源表新增資料同步到目標表

目標表和源表不一致資料，可以透過symbol和tradedate得到，並且源表只有365天之內的資料會被修改

設計思路
1、建立一張臨時表，這個臨時表獲取源表最近365天之內的資料
2、用這張臨時表和目標表進行關聯，必須使用symbol和tradedate兩個欄位同時關聯，update目標表等於臨時表的關聯結果的部分
3、臨時表中存在的行但是不存在目標表，把這部分insert到目標表

程式碼
DECLARE @maxtradedate datetime
DECLARE @LastUpdate datetime
SELECT @maxtradedate = MAX(tradedate) FROM [dbo].[test3]
SET @LastUpdate = DATEADD(dd, - 365, @maxtradedate)
PRINT @LastUpdate

SELECT * INTO #cn FROM algocndbalias.CNDB.dbo.cn_trademap WHERE tradedate > @LastUpdate

UPDATE t SET secid = s.secid, symbol = s.symbol, tradedate = s.tradedate, trde = s.trde
, NewType = s.NewType, tr1 = s.tr1, tr2 = s.tr2, tr3 = s.tr3, volume = s.volume
FROM [dbo].[test3] t INNER JOIN #cn s ON s.symbol = t.symbol and s.tradedate=t.tradedate

INSERT dbo.test1 (secid,symbol,tradedate,trde,NewType,tr1,tr2,tr3,volume)
SELECT secid,symbol,tradedate,trde,NewType,tr1,tr2,tr3,volume
FROM #cn WHERE tradedate NOT IN (SELECT tradedate FROM dbo.test1);

以上第二步思路，update 目標表 inner join 源表，千萬要選擇正確的關聯條件，見如下實驗，如果只關聯一個欄位t.symbol=s.symbol，目標表和源表該關聯條件關聯的有10行，目標表該10行資料都會update和源表一樣;如果關聯兩個欄位t.symbol=s.symbol and t.tradedate=s.tradedate，目標表和源表該關聯條件關聯的有1行，目標表該1行資料會update和源表一樣

建立和test1一樣的test2和test3表，資料也一樣，symbol=000638並且tradedate=2022-09-30的這一行的tr3=20221010
select * from test1 where symbol=000638 order by tr3 desc結果如下
secid	symbol	tradedate	trde	NewType	tr1	tr2	tr3	volume
10001038	000638	2022-09-30	NULL	2022-10-10	20220930	NULL	20221010	0
10001038	000638	2021-06-10	NULL	2021-06-11	20210610	NULL	20210611	0
10001038	000638	2018-02-05	NULL	2018-02-12	20180205	NULL	20180212	0
10001038	000638	2018-02-06	NULL	2018-02-12	20180206	NULL	20180212	0
10001038	000638	2018-02-07	NULL	2018-02-12	20180207	NULL	20180212	0
10001038	000638	2018-02-08	NULL	2018-02-12	20180208	NULL	20180212	0
10001038	000638	2018-02-09	NULL	2018-02-12	20180209	NULL	20180212	0
10001038	000638	2017-07-18	NULL	2018-01-18	20170718	NULL	20180118	0
10001038	000638	2017-07-19	NULL	2018-01-18	20170719	NULL	20180118	0
10001038	000638	2017-07-20	NULL	2018-01-18	20170720	NULL	20180118	0

select * from #cn where symbol=000638結果如下，且該行資料欄位symbol=000638，tradedate=2022-09-30，tr3=20221018
secid	symbol	tradedate	trde	NewType	tr1	tr2	tr3	volume
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0

最初的test1，test2和test3，select * from test1 where symbol=000638的資料有10行，symbol=000638並且tradedate=2022-09-30的只有一行，該行tr3=20221010
我們本來要的是隻是把symbol=000638並且tradedate=2022-09-30的這一行的tr3=20221010改成tr3=20221018

假如我們執行如下

--update表test2
DECLARE @maxtradedate datetime
DECLARE @LastUpdate datetime
SELECT @maxtradedate = MAX(tradedate) FROM [dbo].[test2]
SET @LastUpdate = DATEADD(dd, - 365, @maxtradedate)
PRINT @LastUpdate

SELECT * INTO #cn FROM algocndbalias.CNDB.dbo.cn_trademap WHERE tradedate > @LastUpdate

UPDATE t SET secid = s.secid, symbol = s.symbol, tradedate = s.tradedate, trde = s.trde
, NewType = s.NewType, tr1 = s.tr1, tr2 = s.tr2, tr3 = s.tr3, volume = s.volume
FROM [dbo].[test2] t INNER JOIN #cn s ON s.symbol = t.symbol

執行後的結果test2的10行全部變成了tradedate=2022-09-30，tradedate=2022-09-30，tr3=20221018
select * from test2 where symbol=000638 order by tr3 desc
secid	symbol	tradedate	trde	NewType	tr1	tr2	tr3	volume
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0

--update表test3
DECLARE @maxtradedate datetime
DECLARE @LastUpdate datetime
SELECT @maxtradedate = MAX(tradedate) FROM [dbo].[test3]
SET @LastUpdate = DATEADD(dd, - 365, @maxtradedate)
PRINT @LastUpdate

SELECT * INTO #cn FROM algocndbalias.CNDB.dbo.cn_trademap WHERE tradedate > @LastUpdate

UPDATE t SET secid = s.secid, symbol = s.symbol, tradedate = s.tradedate, trde = s.trde
, NewType = s.NewType, tr1 = s.tr1, tr2 = s.tr2, tr3 = s.tr3, volume = s.volume
FROM [dbo].[test3] t INNER JOIN #cn s ON s.symbol = t.symbol and s.tradedate=t.tradedate

執行後的結果test3的只有一行全部變成了symbol=000638，tradedate=2022-09-30，tr3=20221018，就是symbol=000638，tradedate=2022-09-30這行
select * from test3 where symbol=000638 order by tr3 desc
secid	symbol	tradedate	trde	NewType	tr1	tr2	tr3	volume
10001038	000638	2022-09-30	NULL	2022-10-18	20220930	NULL	20221018	0
10001038	000638	2021-06-10	NULL	2021-06-11	20210610	NULL	20210611	0
10001038	000638	2018-02-05	NULL	2018-02-12	20180205	NULL	20180212	0
10001038	000638	2018-02-06	NULL	2018-02-12	20180206	NULL	20180212	0
10001038	000638	2018-02-07	NULL	2018-02-12	20180207	NULL	20180212	0
10001038	000638	2018-02-08	NULL	2018-02-12	20180208	NULL	20180212	0
10001038	000638	2018-02-09	NULL	2018-02-12	20180209	NULL	20180212	0
10001038	000638	2017-07-18	NULL	2018-01-18	20170718	NULL	20180118	0
10001038	000638	2017-07-19	NULL	2018-01-18	20170719	NULL	20180118	0
10001038	000638	2017-07-20	NULL	2018-01-18	20170720	NULL	20180118	0

select t.symbol,t.tradedate,t.tr3,s.symbol,s.tradedate,s.tr3 from test1 t inner join #cn s on t.symbol=s.symbol and t.symbol=000638 order by t.tr3 desc
源表和目標表t.symbol=s.symbol關聯結果有10行
symbol	tradedate	tr3	symbol	tradedate	tr3
000638	2022-09-30	20221010	000638	2022-09-30	20221018
000638	2021-06-10	20210611	000638	2022-09-30	20221018
000638	2018-02-05	20180212	000638	2022-09-30	20221018
000638	2018-02-06	20180212	000638	2022-09-30	20221018
000638	2018-02-07	20180212	000638	2022-09-30	20221018
000638	2018-02-08	20180212	000638	2022-09-30	20221018
000638	2018-02-09	20180212	000638	2022-09-30	20221018
000638	2018-01-08	20180118	000638	2022-09-30	20221018
000638	2018-01-09	20180118	000638	2022-09-30	20221018
000638	2018-01-10	20180118	000638	2022-09-30	20221018

select t.symbol,t.tradedate,t.tr3,s.symbol,s.tradedate,s.tr3 from test1 t inner join #cn s on t.symbol=s.symbol and t.tradedate=s.tradedate and t.symbol=000638 order by t.tr3 desc
源表和目標表t.symbol=s.symbol and t.tradedate=s.tradedate關聯結果有1行
symbol	tradedate	tr3	symbol	tradedate	tr3
000638	2022-09-30	20221010	000638	2022-09-30	20221018

DELETE test2 FROM test2 s INNER JOIN #cn t ON s.symbol = t.symbol and  t.symbol=000638
--表test2關聯的10行資料被刪除了

DELETE test3 FROM test3 s INNER JOIN #cn t ON s.symbol = t.symbol and s.tradedate=t.tradedate and  t.symbol=000638
--表test3關聯的1行資料被刪除了

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2919083/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

update,delete與INNER JOIN 以及刪除重複資料
2009-10-22
delete
SQL INNER JOIN 關鍵字用法
2013-06-05
SQL
MySQL INNER JOIN關聯多張表的寫法
2017-03-28
MySql
主外來鍵關聯刪除(on delete set null和on delete cascade)
2014-03-10
deleteNull
mssql sqlserver update delete表別名用法簡介
2018-08-04
SQLServerdelete
Update 多個關聯表SQL的寫法
2007-12-10
SQL
關於delete還是update會產生更多日誌的問題
2010-08-18
delete
ORACLE多表關聯UPDATE語句
2018-11-16
Oracle
ORACLE多表關聯UPDATE 語句
2016-10-22
Oracle
PL/sql中怎樣得到insert，update，delete語句執行後，受影響的紀錄行數關鍵字
2010-02-12
SQLdelete
Delete,insert,update與undo的關係[轉載TOM文章]
2013-10-21
delete
關於UPDATE中關聯查詢的執行時間考慮
2014-09-11
MySQL表關聯join方式
2020-11-12
MySql
【SQL】13 SQL 別名、SQL 連線(JOIN)、SQL INNER JOIN 關鍵字、SQL LEFT JOIN 關鍵字、SQL RIGHT JOIN 關鍵字、SQL FULL OUTER J
2018-07-26
SQL
Oracle\MS SQL Server Update多表關聯更新
2018-11-16
OracleSQLServer
myisam對於update，insert,delete關於auto_incremant的影響
2015-12-09
deleteREM
innodb對於update，insert,delete關於auto_incremant的影響
2015-12-09
deleteREM
sqlserver update或者Delete誤操作恢復方法---【驗證】
2017-09-04
SQLServerdelete
避免在程式碼中直接使用delete關鍵字
2008-09-09
delete
Oracle多表關聯更新的方式選擇, Loop or Hash update?
2009-05-22
OracleOOP
請教db4o delete物件後，關聯物件未刪除
2011-04-25
delete物件
表的關聯關係
2018-10-08
PostgreSQL,SQLServer邏輯增量(通過邏輯標記update,delete)同步到Greenplum,PostgreSQL
2018-06-21
SQLServerdelete
表連線時update與delete操作需注意的地方
2009-04-13
delete
恢復update,delete表資料錯誤的語句
2011-10-13
delete
Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE (351)
2007-12-11
delete
三表關聯查詢-多次LEFT JOIN...ON
2013-05-27
mysql 5.5 中自連線update and delete
2012-07-11
MySqldelete
34、VIEW可以insert,delete,update.
2007-12-12
Viewdelete
如何插入insert_update,delete_select特殊字元&到oracle表中
2010-07-10
delete字元Oracle
sql server 帶有OUTPUT的INSERT,DELETE,UPDATE
2018-05-29
SQLServerdelete
單表的更新UPDATE和刪除記錄DELETE（二十六）
2020-04-04
delete
MySQL資料災難挽救之Delete\Update
2021-11-08
MySqldelete
mysql innodb新建索引堵塞update ，insert,delete
2013-02-04
MySql索引delete
利用Open Interface表對BOM、Substitute、Reference Designater 進行Create、Update、Delete操作
2007-02-06
delete
mysql新建表和多表查詢，關鍵字join
2018-07-30
MySql
關於 select ... for update 和 for update nowait
2014-09-16
AI
Oracle\MS SQL Server的資料庫多表關聯更新UPDATE與多表更新
2012-11-15
OracleSQLServer資料庫