Replacing multiple values of same block in SQL

Tejas Shah 0
Viewed 28

I have column Conveyor with conveyor name entries in the table Report_Line which I want to replace with conveyor no.

Belt - 1 | Slack - 2 | Chain - 3

It's real time scenario, as soon as a row is added with data, according to the name of conveyor, it should get replaced with it's respective number.

I tried replace query with Union statement but didn't work, throws error

SELECT TOP 1 * 
FROM Report_Line 
ORDER BY Serial_no DESC

SELECT REPLACE(Conveyor, 'Slack', '2') 
UNION
SELECT REPLACE(Conveyor, 'Belt', '1')
UNION
SELECT REPLACE(Conveyor, 'Chain', '3') 
GO
replace sql-server sql
1 Answers

You could try using an inline, hard-coded table, and join to it by the Conveyor names.

Something like this:

Select Top 1* FROM Report_Line 
Left Join 
(values ('Slack', '2'),('Belt', '1'),('Chain', '3')) sidetable(conveyor_name,id)
on sidetable.conveyor_name = Report_Line.Conveyor
Order by Serial_no DESC
GO
schumacher0
Related