tibberous Posted April 23, 2008 Share Posted April 23, 2008 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... Quote Link to comment Share on other sites More sharing options...
webent Posted April 23, 2008 Share Posted April 23, 2008 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. Quote Link to comment Share on other sites More sharing options...
tibberous Posted April 23, 2008 Author Share Posted April 23, 2008 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'; Quote Link to comment Share on other sites More sharing options...
tibberous Posted April 23, 2008 Author Share Posted April 23, 2008 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! Quote Link to comment Share on other sites More sharing options...
tibberous Posted April 23, 2008 Author Share Posted April 23, 2008 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 ; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.