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, September 25, 2013 2:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
dastagiri16 (9/25/2013)
hi,

any single statement to complete this task


Um...

GilaMonster (9/25/2013)
You can't do it in one statement, because updating a column to one value and then updating it to another require two statements. Though you could just have a single statement that does nothing and it'll have the same effect.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1498182
Posted Wednesday, September 25, 2013 2:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
Koen Verbeeck (9/25/2013)
Sure.

SELECT 'Nothing' FROM myTable;

Et voila, the row still has the value A.


I can simplify that...

;




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1498184
Posted Wednesday, September 25, 2013 3:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:47 AM
Points: 260, Visits: 686


DECLARE @t TABLE(city VARCHAR(1))
INSERT INTO @t(city)
VALUES ('A'),( 'B'),( 'C'),( 'A'),( 'B'),( 'C'),( 'A');

-- do a select and not the results for comparison purposes

SELECT city FROM @t


UPDATE @t
SET city = CASE city
WHEN 'A' THEN 'B'
WHEN 'B' THEN 'A'
else city
END

-- select after update to compare with initial select
select * from @t


SQL 2005/2008 DBA - MCTS/MCITP
Post #1498223
Posted Wednesday, September 25, 2013 6:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
I'll get into the spirit of this thing you've got going here!

CREATE TABLE #MyTable
(
city nchar(10)
);

INSERT INTO #MyTable VALUES ('A');

WITH SampleData (city) AS
(
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'A'
)
UPDATE a
SET city = b.city
FROM #MyTable a
JOIN SampleData b ON 1=1;

SELECT * FROM #MyTable;

GO
DROP TABLE #MyTable;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1498641
Posted Wednesday, September 25, 2013 10:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:40 AM
Points: 54, Visits: 148
hi,

here i am not able to trust it is updated with 'A'

supose in cte last select statement can be changed to 'AA' it is not showing last updated record that is 'AA'


Thanks,
Dastagiri

Post #1498661
Posted Wednesday, September 25, 2013 10:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
dastagiri16 (9/25/2013)
hi,

here i am not able to trust it is updated with 'A'

supose in cte last select statement can be changed to 'AA' it is not showing last updated record that is 'AA'


Thanks,
Dastagiri



In fact, it is only updated once:
http://www.sqlservercentral.com/articles/T-SQL/101464/



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1498662
Posted Saturday, September 28, 2013 7:24 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: Sunday, April 13, 2014 11:53 PM
Points: 714, Visits: 516
@dastagiri16,

I'm sure you was not applying for the job of DBA.


Post #1499686
Posted Sunday, September 29, 2013 4:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
dastagiri16 (9/25/2013)
hi,

one of the interviewer asked the question about updating...so i have added test table like below...

USE [Practice]
GO

/****** Object: Table [dbo].[mytable] Script Date: 09/25/2013 13:10:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[mytable](
[city] [nchar](10) NULL
) ON [PRIMARY]

GO

and i have inserted values like


insert into mytable (city) values ('A')


so now i want to write update querty to update city value from A to B then B to A by using any query....



Not all questions asked by interviewers have a positive answer. Sometimes they ask the "wrong" thing to see what you'll say.

Also, not all interviewers actually have the knowledge to conduct a proper interview. They might actually but incorrectly believe that you can update the same row more than once in the same "query".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1499788
Posted Sunday, September 29, 2013 11:08 PM


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: Sunday, April 13, 2014 11:53 PM
Points: 714, Visits: 516
I have faced the same situation in an interview where interviewer asked me a question on triggers.

I tried my hard to convince him that we have INSTEAD OF and AFTER triggers in MS sql-server, and he was stuck to his point that we also have a BEFORE trigger in MS sql-server.


Post #1499831
Posted Sunday, September 29, 2013 11:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
T.Ashish (9/29/2013)
I have faced the same situation in an interview where interviewer asked me a question on triggers.

I tried my hard to convince him that we have INSTEAD OF and AFTER triggers in MS sql-server, and he was stuck to his point that we also have a BEFORE trigger in MS sql-server.




In cases like that, after you've made your initial point that you don't think they exist, it is probably best to stick with something non-committal like:

"I'd be fascinated to learn about BEFORE triggers from you some time, but for now I can't comment further because I have no experience with them."

One of the guys on this forum has a saying in his signature link that goes something like:

"Never argue with an idiot. They'll drag you down to their level and they've got a lot more experience operating there."



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499835
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse