写一条DELETE语句，实现相同的NAME值，只保留ID最小的记录(解决问题1093 - You can‘t specify target table ‘team‘ for update in )

测试表信息及数据

CREATE TABLE team (
id int(0) NOT NULL AUTO_INCREMENT,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of team

---

INSERT INTO team VALUES (1, ‘A’);
INSERT INTO team VALUES (2, ‘B’);
INSERT INTO team VALUES (3, ‘B’);
INSERT INTO team VALUES (4, ‘A’);
INSERT INTO team VALUES (5, ‘C’);
INSERT INTO team VALUES (6, ‘C’);

执行语句

错误执行

1. IN语句

DELETE FROM team WHERE id IN(
		SELECT aa.id from team aa WHERE aa.id NOT IN (
				SELECT min(a.id) id FROM team a GROUP BY a.`name`
		)
);

2. EXISTS语句

DELETE FROM team WHERE EXISTS(
		SELECT aa.id from team aa WHERE aa.id NOT IN (
				SELECT min(a.id) id FROM team a GROUP BY a.`name`
		)
);

3. NOT IN语句

DELETE FROM team WHERE id NOT IN(
		SELECT min(aa.id) FROM team aa GROUP BY aa.name
);

4. NOT EXISTS语句

DELETE FROM team WHERE NOT EXISTS(
		SELECT min(aa.id) FROM team aa where aa.id = team.id GROUP BY aa.name
);

报错分析

执行报错：1093 - You can’t specify target table ‘team’ for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，
mysql不支持这种方式。oracel和msserver都支持这种方式。

正确执行

1. NOT IN语句

DELETE FROM team WHERE id NOT IN (
    SELECT b.id FROM(
        SELECT min(id) id FROM team t GROUP BY t.name
    ) b
);

2. NOT EXISTS语句

DELETE FROM team WHERE NOT EXISTS(
		SELECT b.id FROM(
        SELECT min(aa.id) id FROM team aa GROUP BY aa.name
    ) b where b.id = team.id
);

注：能力有限，还请谅解，争取早日能够写出有质量的文章！

我是皮蛋布丁，一位爱吃皮蛋的热爱运动的废铁程序猿。

感谢各位大佬光临寒舍~