October 24, 2011 at 10:32 am
Not certain why I selected the "right" answer, but got the question wrong?
I know there is some debate on the syntax, etc... but did not think it was all that.
October 24, 2011 at 11:11 am
Thanks for the question.
Thanks to all who have posted better solutions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 24, 2011 at 11:48 am
syntax is certainly wrong unless it is a char type in the answers. For an int type this works:
UPDATE table1
SET col1 = ABS(col1) - 1
October 24, 2011 at 11:57 am
Kenneth J. Moore (10/24/2011)
syntax is certainly wrong unless it is a char type in the answers.
Even if the column is of a numeric type, the syntax of the correct answer is still not wrong. Inefficient and confusing, yes, but not wrong. The string constants '1' and '0' will both be implicitly converted to int (or whatever other numeric type is used for the column), and since those conversions will succeed, the query will work.
October 24, 2011 at 12:07 pm
Kenneth J. Moore (10/24/2011)
syntax is certainly wrong unless it is a char type in the answers. For an int type this works:UPDATE table1
SET col1 = ABS(col1) - 1
I think you mean this:
UPDATE table1
SET col1 = ABS(col1 - 1)
.
October 24, 2011 at 1:12 pm
Yep, posted a little hastily! Thanks!
October 24, 2011 at 2:18 pm
hakan.winther (10/24/2011)
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
Another solution that is much more efficient than the correlated subquery in the question! 🙂 Personally I didn't vote for the "correct answer" because I was hoping it didn't work.
I eliminated the last 3 options in about 30 seconds... and I had to read the first one over and over.
Yes it works.... but the above is simple and easy to follow. If one of my developers used the answer to do something like this, he and I and his boss would have a long long discussion about SQL basics.
October 24, 2011 at 2:24 pm
Carla Wilson-484785 (10/24/2011)
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.
I'm sorry. I didn't understand how this solution would work, so I tried it out, but it didn't work correctly for me.
I must be missing something?
Oh! I got it!! (Yes, sly!)
Me too... 🙂 Finally got it... Hugo's answer had left me thinking I was missing something, and I read his description below it several times... But yes, given a table with just 1 columns with 1's and 0's in it.. and the ultimate results is to have one less 1 and one more zero, this works. Bravo Hugo!
October 24, 2011 at 2:43 pm
Thought this was a trick question based on the column name. Column1 in the table vs Col1 in the SQL.
October 24, 2011 at 2:48 pm
mtassin (10/24/2011)
Carla Wilson-484785 (10/24/2011)
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.
I'm sorry. I didn't understand how this solution would work, so I tried it out, but it didn't work correctly for me.
I must be missing something?
Oh! I got it!! (Yes, sly!)
Me too... 🙂 Finally got it... Hugo's answer had left me thinking I was missing something, and I read his description below it several times... But yes, given a table with just 1 columns with 1's and 0's in it.. and the ultimate results is to have one less 1 and one more zero, this works. Bravo Hugo!
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'm impressed with all the contributions on how to skin this cat but if we were to vote for our favorite based on readability, I choose this one:
UPDATE table1 SET col1 = case col1 when 1 then 0 else 1 end
.
October 24, 2011 at 3:16 pm
wware (10/24/2011)
I'm impressed with all the contributions on how to skin this cat but if we were to vote for our favorite based on readability, I choose this one:UPDATE table1 SET col1 = case col1 when 1 then 0 else 1 end
Yup, completely agree. (Except that I would uppercase all keywords and add the semicolon terminator - the option to omit it has been deprecated since at least SQL Server 2008, maybe 2005.
UPDATE table1
SET col1 = CASE WHEN col1 = 1 THEN 0 ELSE 1 END;
October 24, 2011 at 3:41 pm
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,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
October 24, 2011 at 3:58 pm
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.
October 24, 2011 at 4:05 pm
Hugo Kornelis (10/24/2011)
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.
Thank you for the explanation and the example.
I see now that's a solution for that particular case and it was very clever.
I should've payed more attention. :blush:
Thanks again.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
October 24, 2011 at 4:08 pm
Impossible is absolutely the correct answer. Col1 does not exist and cannot be updated.
Viewing 15 posts - 46 through 60 (of 76 total)
You must be logged in to reply to this topic. Login to reply