| Author |
Topic |
|
pnpsql
Posting Yak Master
246 Posts |
 Posted - 2011-12-29 : 08:14:59
|
| hi team , i have a table with a city column, i need to update the city like where city name is 'A' then it should be 'B' and when city name is 'B' then it should be 'A'challenge everything |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-12-29 : 08:20:23
|
[code]update atableset city = case when city_name = 'A' then 'B' else 'A' endwhere city_name in ('A', 'B')[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
pnpsql
Posting Yak Master
246 Posts |
Posted - 2011-12-29 : 08:21:23
|
| i do it my way, please help with cte or any other way.... SELECT * FROM abc1 update abc1 set city = ( case when (CITY= 'A') then 'B' else 'B' END )challenge everything |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-12-29 : 08:33:38
|
oh ! i see you mean same column and not city and city_name.what do you mean by "please help with cte or any other way" ?What's the issue with that query ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
pnpsql
Posting Yak Master
246 Posts |
Posted - 2011-12-29 : 08:35:03
|
| this is gentle question, can it be done by any other way.??challenge everything |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-12-29 : 08:51:49
|
this is the easiest way. Not sure why are you looking for in CTE, but cte will not make it any easier
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-29 : 09:23:51
|
From your original post, it seemed like you wanted to swap the city names - i.e., if it is 'A', then you want to change it to 'B' and if it is 'B' you want to change it to 'A'. If that is so, you can do it UPDATE abc1 SETcity = CASE WHEN city = 'A' THEN 'B' WHEN city = 'B' THEN 'A' ENDWHERE city IN ('A','B'); |
|
|
|
pnpsql
Posting Yak Master
246 Posts |
Posted - 2011-12-29 : 09:26:02
|
| ha ha ha...thanks for driving me in right direction..challenge everything |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-29 : 09:45:58
|
You are quite welcome.As an aside, when I first started programming in SQL, I found it very difficult to see how this type of query would work correctly. Coming from a C# world, I thought that:1) it would change all the 'A's to 'B's, and THEN2) it would change all the 'B's (including those that were changed in the prior step) to 'A'sthus ending up with all 'A's.When I realized that that is not the case, that SQL treats it as a SET with a "BEFORE" and "AFTER" concept, I was floored! I stood up and did a hula dance in my office (and luckily no one was around to see)  |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
pnpsql
Posting Yak Master
246 Posts |
Posted - 2011-12-29 : 22:57:01
|
| what does it means...challenge everything |
|
|
|
|
update table query