测试表:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`order`varchar(255)DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
插入测试数据:
INSERT INTO `bbkb_busticket`.`test` (`id`, `name`, `order`) VALUES ('', '张三', '');
INSERT INTO `bbkb_busticket`.`test` (`id`, `name`, `order`) VALUES ('', '李四', '');
INSERT INTO `bbkb_busticket`.`test` (`id`, `name`, `order`) VALUES ('', '王八蛋', '');
交换两行中某个字段的值:(将ID=1、2的两个order替换下)
UPDATE test t1
JOIN test t2
ON (t1.id =2 AND t2.id = 1)
SET t1.`order` = t2.`order`,t2.`order`=t1.`order`;
交换两列中某两列的值:(将ID=1的name 和 order 交换一下)
UPDATE test t1,test t2 SET t1.`name` = t2.`order`,t1.`order` = t2.`name` WHERE t1.id = t2.id