MySql通过update语句交换两行记录非唯一的属性

现有表student,定义如下。

CREATE TABLE `student` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `id_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=11002 DEFAULT CHARSET=latin1;


Id  date    
2   2015-08-26 00:20:32
4   2018-08-26 00:20:32

现在希望将Id=2,Id=4的两条记录中的date进行交换，接着执行下面的查询。

SELECT * FROM 
student AS S1
JOIN student AS S2 ON
S1.id = 4 AND S2.Id = 2
OR 
S1.id = 2 AND S2.Id = 4;

执行后得到结果。

id        date          id1  date1
4   2016-08-26 00:20:32 2   2015-08-26 00:20:32
2   2015-08-26 00:20:32 4   2016-08-26 00:20:32

可以看到要完成交换，只需要将左表和右中的date进行交换，就可以完成两条记录中的date属性值的交换。

查询mysql 手册update定义。

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]

table_reference table_references and where_condition are specified as
described in Section 13.2.9, “SELECT Syntax”.

可以看到table_reference与select中的描述一致，因此在update中可以使用join。

接着将上面的select 改写为update。

UPDATE
student AS S1
JOIN student AS S2 ON (
S1.id = 4 AND S2.Id = 2
OR 
S1.id = 2 AND S2.Id = 4
)
SET S1.date = S2.date;

执行后id = 2,id = 4的两条记录中的完成交换。

上述update语句还可以改写为下面的形式。

UPDATE
student AS S1
JOIN student AS S2 ON
(S1.id = 4 AND S2.Id = 2)
SET
S1.date = S2.date,
S2.date = S1.date;

注意如果所交换的属性加了唯一索引，那么交换无法完成。

参考

1.一条sql交换两行记录http://www.microshell.com/database/sql/swap-values-in-2-rows-sql/

2.一条sql交换两行记录http://stackoverflow.com/questions/4517219/how-to-update-swap-values-of-two-rows-with-single-query

3.mysql update语法 https://dev.mysql.com/doc/refman/5.6/en/update.html