SQL Server:用查找表中的值替换表中的数值

使用您的示例数据:

create table #data
(
    [Statement] varchar(20),
    Answer varchar(10)
)

insert into #data values
('First Name','Mike'),
('Last Name','Smith'),
('Position','Fitter'),
('Country','France'),
('Years Worked','25')

create table #lookup
(
    [Key] varchar(20),
    [Return] varchar(20)
)

insert into #lookup values
('First Name','Redacted'),
('Last Name','Removed'),
('Country','Not Available')

这给出了预期的输出:

select
    d.[Statement],
    coalesce(l.[Return],d.Answer) as Answer
from #data d
left join #lookup l on d.[Statement] = l.[Key]

结果:

当Statement=Key时,我尝试使用联接,并尝试使用SET来匹配列。我设法用Return中的值替换了Answer列中的值,但也用NULL替换了查找列中不匹配的值

我认为您的查询有问题的原因是您使用 LEFT JOIN 和 SET 无任何声明 WHERE 条款:

SELECT *
INTO [Data Table Sanitised]
FROM [Data Table] DATA
LEFT JOIN [Look up] DSL
ON DATA.[Statement] = DSL.[Key]
WHERE DSL.[Key] IS NOT NULL;

和

UPDATE [Data Table Sanitised]
SET [Answer] = (
    SELECT [Return]
    FROM [Look up]
    WHERE [Key] = [Statement]
)
WHERE [Statement] IN (SELECT [Key] FROM [Look up]);