根据子查询中存在的整个记录更新列

Question

如果记录存在于子查询中，我试图将列值更新为'是'，如果不存在则为'否'。我创建的代码和临时表如下所示。

if object_id('tempdb..#t1') is not null drop table #t1
CREATE TABLE #t1 (id nvarchar(max), astate varchar(16), code varchar(16), qual_code_flag varchar(8))
INSERT into #t1 VALUES 
(100.1, 'CA', '0123', null),
(100.2, 'CA', '0124', null), 
(100.3, 'PA', '0256', null),
(200.1, 'MA', '0452', null),
(300.1, 'MA', '0578', null),
(400.2, 'CA', '1111', null),
(500.1, 'CO', '1111', null);
if object_id('tempdb..#t3') is not null drop table #t3
CREATE TABLE #t3 (qual_code varchar(16), qual_state varchar(8))
INSERT into #t3 VALUES 
('0123', ''),
('0124', ''),
('0256', ''),
('0452', ''),
('0578', ''),
('1111', 'CO');
update t1
set qual_code_flag = case when t1.* in (
select t1.*
from #t3 t3
inner join #t1 t1
on  t1.code = t3.qual_code
and (t3.qual_state = t1.astate or t3.qual_state = '')
) then 'yes' else 'no' end
from #t1 t1
select * from #t1

这段代码是我希望能工作的东西，但它抛出了一个错误，因为我试图在t1时更新。* in(子查询)，语法不正确。我知道这个查询不会抛出一个错误，如果我只说，例如，当t1。代码在(子查询)，但我需要的是代码和状态的精确组合是在子查询。如果运行子查询中的内容，您将看到#t1中的7条记录中有6条被返回。这些都是我想更新标志为"是"的所有记录，而在子查询中不存在的记录将具有"否"的标志值。我认为这应该是一个简单的查询，但我还没有提出正确的结果。

Answer 1

不需要在子查询中重新打开目标表:相反，您可以使用exists和相关子查询:

update #t1 t1
set qual_code_flag = 
case when exists (
select 1
from #t3 t3
where t1.code = t3.qual_code and (t3.qual_state = t1.astate or t3.qual_state = '')
)
then 'yes' 
else 'no' 
end

Answer 2

可以直接LEFT Join吗?

if object_id('tempdb..#t1') is not null drop table #t1
CREATE TABLE #t1 (id nvarchar(max), astate varchar(16), code varchar(16), qual_code_flag varchar(8))

INSERT into #t1 VALUES 
(100.1, 'CA', '0123', null),
(100.2, 'CA', '0124', null), 
(100.3, 'PA', '0256', null),
(200.1, 'MA', '0452', null),
(300.1, 'MA', '0578', null),
(400.2, 'CA', '1111', null),
(500.1, 'CO', '1111', null);
if object_id('tempdb..#t3') is not null drop table #t3
CREATE TABLE #t3 (qual_code varchar(16), qual_state varchar(8))

INSERT into #t3 VALUES 
('0123', ''),
('0124', ''),
('0256', ''),
('0452', ''),
('0578', ''),
('1111', 'CO');
UPDATE
T1
SET
T1.qual_code_flag = 
CASE
WHEN T3.qual_code IS NULL  THEN 'No'
ELSE 'Yes'
END
FROM
#t1 T1
LEFT JOIN
#t3 T3 ON T1.code = T3.qual_code AND (T1.astate = T3.qual_state OR T3.qual_state = '')