A should-be-simple mysql query

[tibberous]

You have a table:

 

1 Bill

2 Bob

3 Scott

 

In one query, is there a way to switch `Bill` and `Bob`?

 

And no, this isn't my homework problem I just simplified the problem I'm having.

 

If it help, I found this code, which is supposed to switch the values between columns in a single row:

 

update `table` set `a`=`b`, `b`=@old where (@old:=`a`);

 

I know it would only be like three lines of PHP, but it seems pretty basic not to know...

[webent]

I'm no expert, but it seems to me that if you don't first temporarily store one of the values prior to doing a switch, then you will lose it when you write the value from the other field to it.

[tibberous]

This does it. It uses mysql variables.

 

set @v1 := (select `Name` from `test` where id='2'); set @v2 := (select `Name` from `test` where id='3'); update `test` set `Name`:=@v2 where id='3'; update `test` set `Name`=@v1 where id='2';

[tibberous]

Thats messy as hell, but if you convert it into a stored procedure, it isn't bad at all.

 

I made one named call that takes parameters id 1, id 2 and field name, so I can swap stuff around all day!

[tibberous]

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `swap`(p1 int, p2 int)

begin

set @v1 := (select `Name` from `test` where id=p1);

set @v2 := (select `Name` from `test` where id=p2);

update `test` set `Name`:=@v2 where id=p1;

update `test` set `Name`=@v1 where id=p2;

end;;

DELIMITER ;