Update with CASE statement

Question

Update with CASE statement

Raghavendra Mudugal

SSChampion

Points: 10658
More actions
February 18, 2013 at 9:04 pm

#289488

Comments posted to this topic are about the item Update with CASE statement
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Viewing 15 posts - 1 through 15 (of 54 total)

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

zerko SSCommitted Points: 1524 More actions · Answer 1

As it stands, the question is poorly phrased. Even if one part of the solution is clearly false, the boolean 'OR' operation makes the statement true if the other part is true - "True or False ... 1=1 OR 1=0?" - which rather detracts from a clever example of using the CASE statement to update the column based on its current value.

The question that follows, what would the 'FALSE' answer look like - updates the data unsuccessfully (?) OR fails, and doesn't throw an error?

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

For me the question was simple, clear and very basic. Thanks

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 3

zerko (2/18/2013)
As it stands, the question is poorly phrased. Even if one part of the solution is clearly false, the boolean 'OR' operation makes the statement true if the other part is true - "True or False ... 1=1 OR 1=0?" - which rather detracts from a clever example of using the CASE statement to update the column based on its current value.
The question that follows, what would the 'FALSE' answer look like - updates the data unsuccessfully (?) OR fails, and doesn't throw an error?

I thougth OR here is english , not logical or .. and the caps were used for emphasis ..but, that what I thought..

update woudl happen

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

simple basic question for the day...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

baabhu SSCertifiable Points: 6205 More actions · Answer 5

baabhu

SSCertifiable

Points: 6205

February 18, 2013 at 11:13 pm

#1589036

Seriously?.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 6

Good Question. Again come to know some basic.

keep it up.

Thanks

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 7

Spent 10 minutes looking for the catch, only to realize there wasn't one.

Thanks for the question.

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 8

demonfox (2/18/2013)
zerko (2/18/2013)
As it stands, the question is poorly phrased. Even if one part of the solution is clearly false, the boolean 'OR' operation makes the statement true if the other part is true - "True or False ... 1=1 OR 1=0?" - which rather detracts from a clever example of using the CASE statement to update the column based on its current value.
The question that follows, what would the 'FALSE' answer look like - updates the data unsuccessfully (?) OR fails, and doesn't throw an error?
I thougth OR here is english , not logical or .. and the caps were used for emphasis ..but, that what I thought..
update woudl happen

Thank you zerko and demonfox for the comments as demonfox stated it is completely correct and I did not realize this until now, yes it is not logical, will try to do much better in my next qtod.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Carlo Romagnano SSC-Insane Points: 22761 More actions · Answer 9

The update may fails with error, if the default collation is case sensitive.

UPDATE GenderUpdate

SET Gender = CASE WHEN GENDER = 'M' then 'F' ELSE 'M' END

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 10

I like the question. Thank you!

Best Regards,

Chris Büttner

ldorian81 SSCommitted Points: 1694 More actions · Answer 11

Not confessing to be the best a writing English, personally I use far to many abbreviated/ slang and shortened words (probably due to my age and/or regional location) but this is very poor English.

However I went with true though as the statement would work if ran. :w00t:

Regards
ld

Stoke-on-Trent
United Kingdom

If at first you don't succeed, go to the pub and drink away your current thought plan.

Stuart Davies SSCoach Points: 19505 More actions · Answer 12

Stewart "Arturius" Campbell (2/19/2013)
The intent of the question is good, but the English language syntax used here had me scratching my head for a bit.
<Rant>
True or false requires a statement against which a judgement call must be made, e.g. the SQL command given in the snippet will update the column correctly. True or False
Or, as a question per sê: will the SQL command given in the snippet succeed or fail?
</Rant>
Don't let this discourage you from posting more questions, though. The thought process required always helps clear out cobwebs in preparation for the day.

True - the one thing I have found is making your intent clear, without any assumptions - after all you know what you mean - you wrote it. From my experience this can be difficult enough for native English speakers - let alone in a language that is not your native tongue.

Nice question - thanks

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 13

A straight forward question..

--
Dineshbabu
Desire to learn new things..

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 14

Carlo Romagnano (2/19/2013)
The update may fails with error, if the default collation is case sensitive.
UPDATE GenderUpdate
SET Gender = CASE WHEN GENDER = 'M' then 'F' ELSE 'M' END

Carlo's onto something. If the database does not have a CI collation the statement will fail with something like:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'GENDER'.

Since the database collation in the scenario is unknown, adoption of 3-valued logic would dictate a QotD point be awarded to all those who click 'submit' without selecting either True or False.

Update with CASE statement

Cookies on SQLServerCentral