我有2张桌子,像这样:
CREATE TABLE #targetTable(id int,date datetime,name varchar(50));
CREATE TABLE #sourceTable(id int,date datetime,name varchar(50));
INSERT INTO #targetTable values(1,'1905-07-08 00:00:00.000','John');
INSERT INTO #targetTable values(2,'1905-07-08 00:00:00.000','Albrt');
INSERT INTO #targetTable values(3,'1905-07-08 00:00:00.000','Roy');
INSERT INTO #sourceTable values(1,'1905-07-09 00:00:00.000','jame');
我想更新目标表,当不匹配时,然后用NULL更新目标的名称列。我想要这个结果:
id date name
1 1905-07-09 00:00:00.000 jame
2 1905-07-09 00:00:00.000 null
3 1905-07-09 00:00:00.000 null
我的测试查询无法正常工作:错误:未匹配时不允许进行更新。反正有没有编辑查询以获取结果?
merge into #targetTable a
using #sourceTable b on a.id=b.id
when matched and b.date > a.date then
update
set a.name=b.name,a.date=b.date
when not matched by Target then
update a.date=b.date and a.name = null
它告诉我错误。您能帮我得到结果吗?
阅读问题和所有评论后,您真正想要的是
UPDATE
基于该行id
同时与date
和name
而设置的name
,以NULL
当没有比赛,并设置date
在同一时间与条件,以最大的日期。UPDATE
匹配时使用新值(name
,date
)id
为了简单起见,我在这里使用了一个表变量,但是概念是相同的。
给定表和数据,将产生:
DECLARE @targetTable TABLE (id int,date datetime,name varchar(50));
DECLARE @sourceTable TABLE (id int,date datetime,name varchar(50));
INSERT INTO @targetTable
VALUES
(1,'1905-07-08 00:00:00.000','John'),
(2,'1905-07-08 00:00:00.000','Albrt'),
(3,'1905-07-08 00:00:00.000','Roy');
INSERT INTO @sourceTable values(1,'1905-07-09 00:00:00.000','jame');
SELECT id,date,name FROM @targetTable;
SELECT id,date,name FROM @sourceTable;
id date name
1 1905-07-08 00:00:00.000 John
2 1905-07-08 00:00:00.000 Albrt
3 1905-07-08 00:00:00.000 Roy
id date name
1 1905-07-09 00:00:00.000 jame
您想要的基本上与此选择语句相同:
SELECT
t.id,
CASE
WHEN s.id IS NOT NULL THEN s.[date]
ELSE (SELECT MAX([date]) FROM @sourceTable )
END AS [date],
CASE
WHEN s.id IS NULL THEN NULL
ELSE s.name
END AS [name]
FROM @targetTable AS t
LEFT OUTER JOIN @sourceTable AS s
ON t.id = s.id;
为此,我们可以将其合并到更新中:
UPDATE @targetTable
SET [date] = CASE
WHEN s.id IS NOT NULL THEN s.[date]
ELSE (SELECT MAX([date]) FROM @sourceTable )
END,
[name] = CASE
WHEN s.id IS NULL THEN NULL
ELSE s.name
END
FROM @targetTable AS t
LEFT OUTER JOIN @sourceTable AS s
ON t.id = s.id;
最终输出
SELECT id,date,name FROM @targetTable;
id date name
1 1905-07-09 00:00:00.000 jame
2 1905-07-09 00:00:00.000 NULL
3 1905-07-09 00:00:00.000 NULL
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句