测试表信息及数据
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’);
执行语句
错误执行
- 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`
)
);
- 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`
)
);
- NOT IN语句
DELETE FROM team WHERE id NOT IN(
SELECT min(aa.id) FROM team aa GROUP BY aa.name
);
- 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都支持这种方式。
正确执行
- 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
);
- 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
);
注:能力有限,还请谅解,争取早日能够写出有质量的文章!
我是皮蛋布丁,一位爱吃皮蛋的热爱运动的废铁程序猿。