...

View Full Version : Update...case...when...then



ShMiL
03-23-2007, 03:58 PM
I'm trying to update the table [faq].
This works ok (no order change).


UPDATE faq SET orderID = CASE id
WHEN 1 then 0
WHEN 2 then 1
WHEN 3 then 2
WHEN 5 then 3
WHEN 4 then 4
WHEN 6 then 5
ELSE orderID END

But when I try to change the order, like this:


UPDATE faq SET orderID = CASE id
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 5 THEN 3
WHEN 4 THEN 4
WHEN 6 THEN 5
ELSE orderID END

I get this error:

Duplicate entry '4' for key 'orderID'

How is that?

Thanks

ShMiL
03-23-2007, 04:06 PM
found a solution.

because MySQL does the update LIVE, it happens to be that duplications may accure.
The solution is ofcourse to make the orderID index not unique.

Fumigator
03-23-2007, 09:52 PM
Wouldn't it just be easier to make the ID unique and auto-increment and then not worry about its value?

Making an ID non-unique is usually a bad idea.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum