结论
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行数据被删除了