October 24, 2011 at 4:13 pm
jshahan (10/24/2011)
Impossible is absolutely the correct answer. Col1 does not exist and cannot be updated.
The question was how to update column1, not col1. So impossible is absolutely NOT the correct answer.
Tecnically speaking, the other three answers are incorrect as well. But once the fourth is ruled out, the only explanation that remains is a human error when entering the question.
October 24, 2011 at 4:16 pm
Fine. Show me code that will update Col1 in a table that where that column doesn't exist and I'll agree with you.
October 24, 2011 at 4:22 pm
jshahan (10/24/2011)
Fine. Show me code that will update Col1 in a table that where that column doesn't exist and I'll agree with you.
Below is a cut and paste of the question. Please show me where it says that I have to update Col1. I must have overlooked it.
Update table
Table1
Column1
--------------------------
1
0
1
1
1
0
0
Which code segment will update column1 and set the column1 value to 1 where the column1 value is 0 and vice versa.
October 24, 2011 at 4:29 pm
I was going to point out that you could only choose among the available responses and ‘Impossible” is the best answer. But since you are able to discern what the author was thinking and I lack such powers, I believe I’ll let this one go…
October 24, 2011 at 5:46 pm
I did it using a little math too, like an earlier reader's post:
update Table1
set Column1 = (-1 * Column1) + 1
October 24, 2011 at 9:28 pm
Interesting discussion.
I especially like the bitwise operation... pretty clever.
One thing that is missing is the data type of column1 - this affects some of the answers. If it is a bit, they work. But if it's an int, some don't.
What I like is using the fact that for a bit column, a 0 is false and all non-zero values are converted to true... aka 1.
So, these work:
If column1 is an integer-based:
UPDATE table1 SET column1 = CONVERT(BIT, column1-1);
and if column1 is a bit:
UPDATE table1 SET column1 = column1-1;
For both, the 1's get converted to zero, and the zeros get converted to -1... which get implicitly converted to true since it's a non-zero value... or 1.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 24, 2011 at 11:08 pm
Not possible as all the available answers refer to Col1 but Col1 does not exist in the Table.
October 25, 2011 at 6:54 am
Hugo Kornelis (10/24/2011)
codebyo (10/24/2011)
wware (10/24/2011)
OK, I just got it, thanks to mtassin's explanation and my reread of Hugo's original explanation ("there are no other columns in the table.") In this scenario, because there are no other columns, we don't care which rows are the 1s and which rows are the 0s.I still don't get Hugo's code. Am I missing something?
Is it made for tables with just one row?
I must be missing something really important there. 🙂
Best regards,
In the relational model, there is no order of a table. Without an ORDER BY, rows can be returned in any order the optimizer sees fit. So while you and I see the original data in the question in a specific order (first a 1, then a 0, then three 1's and finally two 0's), any other ordering of these rows is still the same table. Basically the table is a bag with 7 rows, three of them containing a 0 and the other four containing a 1.
The intention of the question is to "flip" the bit in each of he 7 rows. So each of the three 0's become 1's, and each of the four 1's become 0's. Our human mind wants to retain the order and flip all the bits (so you get a 0, a 1, three 0's, two 1's), but since there is still no ordering, the actual end result is a bag that still has 7 rows (obviously), but now there are four 0's and three 1's.
My query does not flip all the bits. It flips only one, from 1 to 0. The end result is a bag with 7 rows, four 0's and three 1's. Exactly what the author of the question wanted, but achieved in a totally different way.
Smart Hugo! You set me thinking hard initially, because I was wondering how your UPDATE statement would solve the problem. Like you rightly said, the human mind would naturally try to order it in the same order the question came in.
We've got whiz folks here 🙂
October 25, 2011 at 1:22 pm
There isn't a column named col1 on table1, only column1. Therefore none of the 3 statements will work.
October 26, 2011 at 1:26 am
Got it right by elimination, but as others have pointed out, there are for more efficient/elegant solutions for this problem.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 26, 2011 at 10:35 pm
I also got it correct through elimination of answers.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
October 27, 2011 at 8:18 am
No Doubt, Gentleman its an good question. Another alternate answer for this is
UPDATE table1 SET col1 = case col1 when '1' then '0' else '1' end
Regards,
Vineet Yadav
Sr. Programmer
October 28, 2011 at 6:23 am
Yes, that would be my preferred solution.
November 2, 2011 at 3:59 am
This query has its better candidates.
November 3, 2011 at 8:19 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
I also prefer this simple way to do update. The solution provided by author works fine but it costs more as it is a correlated sub query. Thanks for this nice question.
Viewing 15 posts - 61 through 75 (of 76 total)
You must be logged in to reply to this topic. Login to reply