Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

how to update column city value from 'A' to 'B' and 'B' to 'A' in single query Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 5:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:08 PM
Points: 529, Visits: 1,563
Depending on the interviewer's definition of "query" it could be
BEGIN TRAN
UPDATE ADDRESS SET CITY = 'B'
ROLLBACK TRAN

Post #1503024
Posted Wednesday, October 9, 2013 5:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:23 AM
Points: 110, Visits: 775
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?
Post #1503031
Posted Thursday, October 10, 2013 4:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, July 19, 2014 3:27 AM
Points: 84, Visits: 20
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
Post #1503447
Posted Thursday, October 10, 2013 4:59 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:05 AM
Points: 718, Visits: 542
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.
Post #1503466
Posted Thursday, October 10, 2013 11:31 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 605, Visits: 1,691
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!


update myTable
set City =
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.
Post #1503871
Posted Friday, October 11, 2013 12:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 13,261, Visits: 10,140
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!


update myTable
set City =
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!




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1503886
Posted Monday, October 21, 2013 6:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:49 AM
Points: 225, Visits: 359
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!!!!
Post #1506642
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse