Update table

Question

Post reply

Update table

prashant-507002

SSC Eights!

Points: 922
More actions
October 22, 2011 at 2:21 pm

#246815

Comments posted to this topic are about the item Update table
October 24, 2011 at 2:11 am

This was removed by the editor as SPAM

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Nice question

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

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

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,

Andre Guerreiro Neto

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

xiewei Default port Points: 1498 More actions · Answer 3

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!

Andrew G SSChampion Points: 12809 More actions · Answer 4

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?

vk-kirov SSCertifiable Points: 7686 More actions · Answer 5

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

Adam Phipps Old Hand Points: 397 More actions · Answer 6

Adam Phipps

Old Hand

Points: 397

October 23, 2011 at 11:09 pm

#1398504

How about...

Update table1 set col1 = col1^1

shyam.kishore SSC Veteran Points: 253 More actions · Answer 7

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

charlietuna Ten Centuries Points: 1277 More actions · Answer 8

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

tommyh SSCertifiable Points: 6302 More actions · Answer 9

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

SQLWinther SSCertifiable Points: 6294 More actions · Answer 10

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

Carlo Romagnano SSC-Insane Points: 22603 More actions · Answer 11

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!

SQLWinther SSCertifiable Points: 6294 More actions · Answer 12

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

DugyC Hall of Fame Points: 3804 More actions · Answer 13

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]