Update table

• Comments posted to this topic are about the item Update table

• Nice question

If everything seems to be going well, you have obviously overlooked something.

Ron

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

Thank you for the question.

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

• UPDATE table1 SET col1 = (select distinct case a.col1 when '1' then '0' else '1' end from table1 a where a.col1=table1.col1)

The solution is very clever!

• I understand that the answer works, but can someone explain the process for it?

Is it:

Join the original table with a derived table, using CASE to swap the values (which occurs after the join condition), DISTINCT for some reason and then UPDATE using the correlated subquery?

• Oh, boy! I hope no one will use this code in production! :blink:

Update table1 set col1 = col1^1

• "Update table1 set col1 = col1^1" is really great one as it is also work for table variable.

• 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

• xiewei (10/23/2011)

UPDATE table1 SET col1 = (select distinct case a.col1 when '1' then '0' else '1' end from table1 a where a.col1=table1.col1)

The solution is very clever!

No its not. Its infact really bad since its ALOT more then whats needed to do this operation. There are several examples below which are alot better.

/T

• CELKO (10/23/2011)

UPDATE Table1

SET col1 = -(col1 - 1);

Why make it so insanely complex? Here is a good programming exercise. Given table with {-1, 0, +1} in a column, write expressions to yield all possible mapping of the set to the three values. For exampolke

{+1, 0, -1}

is

UPDATE Table1

SET col1 = -col1 ;

Excellent solution! Much more efficient.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform

• 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 agree!

UPDATE table1 SET col1 = case col1 when '1' then '0' else '1' end

KISS: Keep It Stupid & Simple!

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

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform

• As an example of a bit flip flop it fails on so many levels, but as an example of using case in an update statement it works for me.

Good question, cheers.

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

• This was removed by the editor as SPAM

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