UPDATE students
SET name = other_name,
    other_name = name;

-- 新增列
ALTER TABLE students
ADD COLUMN swap_col VARCHAR(255);

-- 賦值
UPDATE students
SET swap_col = other_name;

UPDATE students
SET other_name = name;

UPDATE students
SET name = swap_col;

-- 刪除列
ALTER TABLE students
DROP COLUMN swap_col;
-- 

-- 創(chuàng)建臨時表
CREATE TEMPORARY TABLE `students_temp`
(
    `id`         int,
    `name`       varchar(255),
    `other_name` varchar(255),
    INDEX `idx_id` (`id`),
    INDEX `idx_name` (`name`),
    INDEX `idx_other_name` (`other_name`)
);

-- 給臨時表賦值
INSERT INTO students_temp
SELECT id, name, other_name
FROM students;

-- 聯(lián)表更新
UPDATE students AS target
    INNER JOIN students_temp AS source
    ON target.id = source.id
SET target.name       = source.other_name,
    target.other_name = source.name;

-- 刪除臨時表
DROP TABLE students_temp;

DROP table if exists `students`;

CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `other_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);

INSERT INTO `students` VALUES (1, '張三', '張三1');
INSERT INTO `students` VALUES (2, '李四', '李四1');
INSERT INTO `students` VALUES (3, '王五', '王五1');
INSERT INTO `students` VALUES (4, '趙六', '趙六');
INSERT INTO `students` VALUES (5, '孫七', '孫七');
INSERT INTO `students` VALUES (6, '張三', '張三2');
INSERT INTO `students` VALUES (7, '李四', '李四2');
INSERT INTO `students` VALUES (8, '張三', '張三3');