SQL Server Replacing values in a table with values in a look up table

6tdlim6h 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(102)

I have data stored in a table that requires specific values to be removed so it can be shared more widely.
The data that needs to be removed will have a key as below:

Data Table
| Statement | Answer |
| ------------ | ------------ |
| First Name | Mike |
| Last Name | Smith |
| Position | Fitter |
| Country | France |
| Years Worked | 25 |

Look up

Key	Return
First Name	Redacted
Last Name	Removed
Country	Not Available

Output:

Statement	Answer
First Name	Redacted
Last Name	Removed
Position	Fitter
Country	Not Available
Years Worked	25

I tried using a join and I tried using SET to match the columns when Statement = Key. Both of these I managed to replace the values in the Answer column with values in Return but it also replaced the values that didn't have a match in the look up column with NULL

Code I have tried:

SELECT *
FROM [Data Table] DATA
LEFT JOIN [Lookup up] DSL
ON DATA.[Statement] = DSL.[Key]

and

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

Both returned:

Statement	Answer
First Name	Redacted
Last Name	Removed
Position	NULL
Country	Not Available
Years Worked	NULL

sql-server

来源：https://stackoverflow.com/questions/77214764/replacing-values-in-a-table-with-values-in-a-look-up-table

1条答案

按热度按时间

oxf4rvwz1#

Using your example data:

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')

This gives the expected output:

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

Result:

Statement	Answer
First Name	Redacted
Last Name	Removed
Position	Fitter
Country	Not Available
Years Worked	25

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Replacing values in a table with values in a look up table

1条答案

相关问题

热门标签

最新问答