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

Question

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

Viewing 15 posts - 16 through 30 (of 36 total)

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

@dastagiri16,

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

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.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

You are right Dwain.

In such cases, we are mostly in a lose-lose situation. Either we will be rejected as we are not as idiot as we should be, or we will be selected to be ruled by an idiot.

If you are lucky then interviewer will reply at last "I was just cross verifying your knowledge."

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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

Some background reading for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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.

That's when I'd smile, ask them if they have $5 to bet on it and a machine with Books Online on it to prove it. After I'd gotten my $5, I'd walk out of the interview. I've worked a places like that before and I don't want to do that again.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

It seems we've moved into the "Dating Your Employer" thread 🙂

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Tom Bakerman (10/8/2013)
It seems we've moved into the "Dating Your Employer" thread 🙂

Seems only natural... the original post was an interview question. 😀

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 11

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

Of course, this would be a rather pathetic discussion on thin ice, but at least it could bring you in the position of "it depends" (on the definition of the term "statement" in the given context) instead of "it's not possible" at all 😉

And, as soon as "statement" is defined more precisely as "DML statement", the "back door" is closed and locked leaving a more detailed question:

Based on the example provided by dwain a few posts back, if we update a "left" table in a 1 ..n relationship, will sql server perform n updates or will it just do a single update? Will this example really change the value to 'B' and back to 'A'? How would you verify?

It's a rather weird original question. But with quite some room to open a discussion. Hidden. But available. 😎

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

LutzM (10/8/2013)
if we update a "left" table in a 1 ..n relationship, will sql server perform n updates or will it just do a single update?

Single.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

LutzM SSC Guru Points: 107049 More actions · Answer 13

GilaMonster (10/8/2013)
LutzM (10/8/2013)
if we update a "left" table in a 1 ..n relationship, will sql server perform n updates or will it just do a single update?
Single.

I know. But I thought it would make a good question. 😉

Unfortunately, answered immediately. :crying: (as it is common here at SSC...)

But the list of questions can be continued:

If it's a single update, which value will be used? Will it be consistent/reproducable or may it vary under certain conditions (e.g. parallelism)?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

LutzM (10/8/2013)
Will it be consistent/reproducable or may it vary under certain conditions (e.g. parallelism)?

The latter. Which is why those many-one updates have such a bad rap (deservedly). It's essentially another order without an order by scenario.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 15

The question asked to OP by interviewer was probably like

"flip the values from A to B and B to A "

was actually answered by "Koen Verbeeck " in his first post here.

AS this is (i often experience this) very frequently asked question to people who are having exp from 3 to 5 yrs.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)