Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have a table named tbl_LivingStyle with 3 columns namely

DomainUID
DOmainUID1
DomainUID2

whose values are

Transport
Fitness
Accomodation

i need to write a single query to update that table such that
DomainUID should be set to metro where DomainUID is Transport

DomainUID1 should be set to Cardio where DomainUID1 is Fitness

DomainUID2 should be set to Hostel where DomainUID2 is Accomodation


this needs to be done in a single update ..Any inputs please?

What I have tried:

currently writing 3 different update queries for updating 3 different columns
Posted
Updated 23-Nov-22 23:49pm

1 solution

You can do it with CASE WHEN but it's messy, and probably not particularly readable:
SQL
UPDATE MyTable SET DomainUID = CASE WHEN DomainUID = 'Transport' then 'metor' ELSE DomainUID END, SET DomainUID = CASE WHEN DomainUID = ...

Me? I'd either write an SP or stick to 3 queries.
 
Share this answer
 
Comments
Richard Deeming 24-Nov-22 8:29am    
Multiple queries (wrapped in a transaction) is obviously the correct solution; but if you're forced to do it like this, at least add a WHERE clause so you only touch the records which are going to be updated. :)
UPDATE MyTable SET ... WHERE DomainUID = 'Transport' OR DomainUID1 = 'Fitness' OR DomainUID2 = 'Accommodation'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900