MYSQL-交换表中2行2字段的值

需求场景：

当表中的一个字段存储当前记录的顺序或者排名时候，它需要进行上移或者下移，或者直接和某个记录交换值的时候，通常可以立刻想到以下的SQL操作语句。      

CREATE TABLE coordinatePt_Table
(
nID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
nIndex SMALLINT UNSIGNED NOT NULL COMMENT '顺序',
Longitude DOUBLE COMMENT '经度',
Latitude DOUBLE COMMENT '纬度',
PRIMARY KEY(nID)
);

DELETE FROM coordinatePt_Table WHERE nID < 100;
INSERT INTO coordinatePt_Table VALUES
(NULL, 0, 30.10, 110.0),
(NULL, 1, 34.10, 115.0);

SELECT * FROM coordinatePt_Table LIMIT 100;

SET @nTemp1 = 0;
SET @nTemp2 = 0;
SELECT nIndex INTO @nTemp1 FROM coordinatePt_Table WHERE nID = 1;
SELECT nIndex INTO @nTemp2 FROM coordinatePt_Table WHERE nID = 2;
UPDATE coordinatePt_Table SET nIndex = @nTemp2 WHERE nID = 1;
UPDATE coordinatePt_Table SET nIndex = @nTemp1 WHERE nID = 2;

记录肯定有更简单的方式实现需求。

UPDATE
coordinatePt_Table AS T1 JOIN
coordinatePt_Table AS T2 ON (T1.nID = 1 AND T2.nID = 2)
SET T1.nIndex = T2.nIndex, T2.nIndex = T1.nIndex; 

 

MYSQL查询语句：