October 24, 2011 at 2:43 am
Or if col1 isn't a bit:
UPDATE table1 SET col1 = ~cast(col1 as bit) where col1 in (0,1)
(no I know you wouldn't do it this way, but then you wouldn't do it the way described in the question either!)
October 24, 2011 at 3:11 am
The question asked to amend the value in Column1, yet options 1,2 & 3 used a field called col1, so leaving #4 as an answer - not possible ... using any of the code options given.
October 24, 2011 at 3:36 am
Nice question ... and thanks for the alternatives posted by other users.
October 24, 2011 at 4:14 am
I have to agree with several of the other posters...
1) The question should have been impossible to answer based on the information given, as the table definition did not match any of the given answers.
2) Even if the definitions matched, the "correct" solution is nothing I'd expect to see in production code (beer fines would likely be levied at my workplace...). Yes, it works. No, it shouldn't be presented as an example of how to do that operation nor as a test of how good your SQL knowledge is, as it's not efficient when compared to using a bitwise NOT or XOR operator instead.
It definitely felt like a square peg/round hole question to me.
October 24, 2011 at 4:16 am
I think there are more complex cases where you need to do the kind of thing discussed in the question - using a subquery - but I did immediately think why not just a simple CASEd flip? (Same as charlietuna essentially)
UPDATE table1 SET col1 = CASE WHEN col1 = 0 THEN 1 ELSE 0 END
I am glad I don't appear to have missed something!
October 24, 2011 at 4:31 am
I agree with several others that there are much easier and much more elegant ways to achieve this. The code in the correct answer would not get past me if I were doing code reviews!
I don't agree with the people who complain about the change from "Column1" to "Col1". If you look at the question and the list of possible answers, it's immediately obvious that this is a mixup on the part of the question's author, not a deliberate ploy to test if people would notice the name change. If the latter were the case, there must have been at least one answer that somehow reflects this.
I won't say that this question is a good question for the reason mentioned in the first paragraph, but it's definitely a question where there can be no discussion over what the correct answer should be. In spite of the error in the column name, there are three answer options that are obviously wrong, and one answer option that is obviously right (once the column name is corrected).
PS: There's even one more way to achieve the intended change:
UPDATE TOP(1) Table1
SET Column1 = 0
WHERE Column1 = 1;
Since row order in a relational table does not matter and there are no other columns in the table to identify rows, the end result of this query will be a table with 7 single-column rows, four of them with the value 0 and three with the value 1. Just like the result of all the other options presented in this topic.
October 24, 2011 at 4:40 am
nice question!!!
October 24, 2011 at 4:54 am
1) UPDATE table1 SET col1 = (select distinct case a.col1 when '1' then '0' else '1' end from table1 a
where a.col1=table1.col1)
2) Update table1 set col1='1' where col1='0'
Both upper scripts are doing same work except first showing message 'n rows affected' where n is maximum no of records in table. But you will run select query at table then then you will get few records affected which was making satisfying the condition. I am not sure which one is better performance perspective.
3) update table1 set col1='1' where col1='0' union update table1 set col1='0' where col1='1'
Absolutely wrong. As we cant use Union in update statement.
Thanks,
Vivek Chaurasia
October 24, 2011 at 5:14 am
codebyo (10/23/2011)
I didn't test the answer but I knew the following:#2: UNION is not allowed to be used that way;
#3: That's not enough and will make things worse as every col1 will have the same value;
#4: We know that nothing is impossible. 😀
Got it right by elimination.
Yep... I hate to admit to it, but that was almost exactly my thought process. 🙂 I'd gotten as far as kicking open Management Studio this morning to test, but decided 'not possible' wasn't possible, 'cause it wouldn't be much of a learning experience if that were the answer. 😀
October 24, 2011 at 5:42 am
Today we learned a way how not to solve such kind of tasks. 😀
October 24, 2011 at 5:54 am
It's a nice easy question, but I have to agree with Joe:
CELKO (10/23/2011)
UPDATE Table1SET col1 = -(col1 - 1);
Why make it so insanely complex?
I agree the given answer is insanely complex. But Joe's is still too complex. Avoid that unary minus!
UPDATE Table1 set col1 = 1-col1
does the job.
Tom
October 24, 2011 at 6:12 am
Hugo Kornelis (10/24/2011)
PS: There's even one more way to achieve the intended change:
UPDATE TOP(1) Table1SET Column1 = 0
WHERE Column1 = 1;
Since row order in a relational table does not matter and there are no other columns in the table to identify rows, the end result of this query will be a table with 7 single-column rows, four of them with the value 0 and three with the value 1. Just like the result of all the other options presented in this topic.
You are a devil. Very elegant solution.
October 24, 2011 at 6:38 am
charlietuna (10/23/2011)
The solution seems a bit opaque. Something like this is (to me) much more straightforward for the next person who has to read it:update #t
set col1 = case col1 when 1 then 0
when 0 then 1
else col1
end
This is what I have done in the past seems simple enough.
October 24, 2011 at 6:45 am
I agree - a simple case statement seems much more straight forward. Is there a reason why the subquery would be more effective?
October 24, 2011 at 6:58 am
I've seen a lot of databases where the flags were typed as var/char fields. I was even mildly concerned that the column name in the question (column1) didn't match the column name in the solution (col1)
Viewing 15 posts - 16 through 30 (of 76 total)
You must be logged in to reply to this topic. Login to reply