Forum Moderators: open
I'm trying to swap the contents of some columns in a row.
UPDATE data SET backup=current, bkcount=count, current=backup, count=bkcount WHERE id=123
Unfortunately, this sets both columns to the same value. What is the correct way to do this without resulting to multiple queries?
Thanks!
Try (after testing on a backup of course):
UPDATE data d, data2 d2,
SET
d.backup=d2.current,
d.bkcount=d2.count,
d.current=d2.backup,
d.count=d2.bkcount
WHERE
d.id=d2.id
AND
d.id=123
