updating two rows at the same time?

mysql help!

[PokeTech]

Well I have these two rows:

INSERT INTO `rpg_user` VALUES (4, 'blah', '001', '1');
INSERT INTO `rpg_user` VALUES (5, 'blah', '010', '24');

What I want to do is change the ID's (which is the first field) at the same time. So it'd be doing something like this:

UPDATE rpg_user SET id='5' WHERE id='4'
UPDATE rpg_user SET id='4' WHERE id='5'

But it doesn't work right. So is there anyway I can do this all at once?

[penders]

Your 2nd UPDATE statement looks as if it will undo the 1st.

Are you trying to swap the IDs of the 2 rows? If so, then I would have thought you'd need to set the row with an id='4' to a temporary value first...

UPDATE rpg_user SET id='TEMP-ID' WHERE id='4';
UPDATE rpg_user SET id='4' WHERE id='5';
UPDATE rpg_user SET id='5' WHERE id='TEMP-ID';

3 separate statements, not 1. To be honest though, this doesn't feel right - I would have thought this sort of thing was best avoided, particulary if id is a primary key?

[PokeTech]

Hmm.. Didn't think about that but it should work. I was thinking I might use a different variable or something. But thanks I'll give it a try.