Click here to Skip to main content
15,797,721 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


whose values are


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
Updated 24-Nov-22 0:49am

1 solution

You can do it with CASE WHEN but it's messy, and probably not particularly readable:
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
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