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

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

    BEGIN TRAN

    UPDATE ADDRESS SET CITY = 'B'

    ROLLBACK TRAN

  • 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?

  • 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

  • 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:

  • 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.

  • 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

  • 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!!!!

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

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