Update with CASE statement

Question

Update with CASE statement

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

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

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 1

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

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 2

Thanks for the question. I knew that the update would work but it took me a while to figure out exactly how I should answer, True or False! I guess it was clear enough because I got it correct.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 3

Koen Verbeeck (2/18/2013)
Spent 10 minutes looking for the catch, only to realize there wasn't one.

+1 and since I just got up, I figured I was missing something. So, I clicked FALSE, figuring that if the answer was TRUE, I couldn't see the point in asking the question.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

no longer ssc user Right there with Babe Points: 780 More actions · Answer 4

A true or false question, that doesn't ask one question in it, can be a little difficult to understand.

Nice.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 5

Leonidas199x (2/19/2013)
A true or false question, that doesn't ask one question in it, can be a little difficult to understand.
Nice.

The question is "does the statement will succeed by updating the data successfully OR it fails by throwing error? "

Which must be TRUE because of the OR.

Bryant McClellan SSCarpal Tunnel Points: 4337 More actions · Answer 6

Andrew Diniz (2/19/2013)
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. 😉

@andrew makes a very good point. Perhaps it would be good practice to have a set of assumptions around QOTD and have them spelled out...version, collation, that sort of thing.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson

no longer ssc user Right there with Babe Points: 780 More actions · Answer 7

(Bob Brown) (2/19/2013)
Leonidas199x (2/19/2013)
A true or false question, that doesn't ask one question in it, can be a little difficult to understand.
Nice.
The question is "does the statement will succeed by updating the data successfully OR it fails by throwing error? "
Which must be TRUE because of the OR.

I could argue with you, but, it is easier to just say that this question wasn't phrased in the best way. Based on the grammar, I would guess that the contributors fist language is not English, so, it is excusable.

It reminds me of the sort of question Sage would throw in one of their exams, if anyone has ever experienced that? They do not have the excuse of English being a second language however.

TomThomson SSC Guru Points: 104773 More actions · Answer 8

The phrasing is a bit bizarre.

I guessed that the environment had to be case insensitive (otherwise statement 3 fails and aborts the batch, so statement 4 never happens). And guessed that the question was whether the upodate would do the right thing. Clearly it will, so picked TRUE.

Tom

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 9

I agree that the wording was not the best, but I was able to figure out the intent.

I am stuck on the update itself:

-- #4 Final update with case statement

UPDATE GenderUpdate

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

I realize that the sample data given only included "M" and "F" values - but if there were any others present, this update wouldn't meet the stated objective.

Maybe I got too much sleep last night...:unsure:

Vel.Velez SSC Journeyman Points: 80 More actions · Answer 10

In SQL Server 2005, wouldn't this FAIL?

--#2 Data

INSERT INTO GenderUpdate (Gender)

VALUES ('M'), ('M'),('M'),('M'),('M'),('M'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F')

If so, #4 would not get to execute?

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 11

Since statement #4 either works OR it doesn't, a false answer would be impossible.

That's just my boolean mind at work.

Thanks for the question, and the nice use of CASE.

Andre Ranieri

sestell1 SSChampion Points: 10230 More actions · Answer 12

Andre Ranieri (2/19/2013)
Since statement #4 either works OR it doesn't, a false answer would be impossible.
That's just my boolean mind at work.
Thanks for the question, and the nice use of CASE.
Andre Ranieri

That was my thinking as well, although I suppose it is possible that it would fail to perform the update in the expected manner without actually raising an error.

sestell1 SSChampion Points: 10230 More actions · Answer 13

Andrew Diniz (2/19/2013)
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. 😉

Good point, although technically (due to the wording of the question) the answer would still be true. 😉

jdamm SSC Veteran Points: 282 More actions · Answer 14

Vel.Velez (2/19/2013)
In SQL Server 2005, wouldn't this FAIL?
--#2 Data
INSERT INTO GenderUpdate (Gender)
VALUES ('M'), ('M'),('M'),('M'),('M'),('M'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F')
If so, #4 would not get to execute?

Correct - checking my results, my default instance is SQL 2005, and this line failed. Went over to the SQL 2008 R2 and no issues.

J

jdamm SSC Veteran Points: 282 More actions · Answer 15

I enjoyed the question. Keep up the work in writing them (I can never seem to come up with a good question idea myself...)

As for writing the question, I would have moved the select to the end, then asked "What is the output of Select Statement?"

with options:

a) F 10

M 6

b) F 6

M 10

c) F 10

d) M 10

J