how to update column city value from 'A' to 'B' and 'B' to 'A' in single query

Question

Post reply

how to update column city value from 'A' to 'B' and 'B' to 'A' in single query

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

dan-572483 SSCoach Points: 15870 More actions · Answer 1

Depending on the interviewer's definition of "query" it could be

BEGIN TRAN

UPDATE ADDRESS SET CITY = 'B'

ROLLBACK TRAN

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 2

LutzM (10/8/2013)
If you insist doing it "in single statement", how about this:
EXEC (...).
If we'd agree this would qualify as a single statement, we're all set. Right? 😀
To "support my argument": BOL qualifies EXEC as a statement: ...SQL Server extends the EXECUTE statement...

In this vein, is a BEGIN .. END block considered a single statement?

nitinkachhwaha SSC Enthusiast Points: 114 More actions · Answer 3

Hi,

Please find the query to update the cty to 'A' where cty is 'B' and from cty 'B' to 'A'.

BEGIN TRAN

SELECT *FROM CITY

UPDATE CITY

SET CTY = CASE WHEN CTY = 'A' THEN 'B'

WHEN CTY ='B' THEN 'A'

END

SELECT *FROM CITY

ROLLBACK

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 4

nitinkachhwaha (10/10/2013)
Hi,
Please find the query to update the cty to 'A' where cty is 'B' and from cty 'B' to 'A'.
BEGIN TRAN
SELECT *FROM CITY
UPDATE CITY
SET CTY = CASE WHEN CTY = 'A' THEN 'B'
WHEN CTY ='B' THEN 'A'
END
SELECT *FROM CITY
ROLLBACK

Well done. Great reply.:hehe:

Fal Hall of Fame Points: 3143 More actions · Answer 5

dastagiri16 (9/25/2013)
hi,
I have a table like
id city
1 A
so i want to update city column from A to B and again B to A by in single statement..please help.
Thanks
Dastagiri

So, update it twice, use single statement. No problem!

updatemyTable

setCity =

case

when City = A then B

when City = B then A

end

go 2

According to BOL "GO is not a Transact-SQL statement; it is a command" 😉

Fal.

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 6

Fal (10/10/2013)
dastagiri16 (9/25/2013)
hi,
I have a table like
id city
1 A
so i want to update city column from A to B and again B to A by in single statement..please help.
Thanks
Dastagiri
So, update it twice, use single statement. No problem!
updatemyTable
setCity =
case
when City = A then B
when City = B then A
end
go 2
According to BOL "GO is not a Transact-SQL statement; it is a command" 😉

Genius! 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

david.john.gilligan Default port Points: 1471 More actions · Answer 7

from BOL: "GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor."

That is nice!!!!