MySQL8 1093 - You can‘t specify target table ‘goods‘ for update in FROM clause

删除或者更新一张表中的数据的时候，会出现1093出错。
原因：不能先查出需要更新或者删除表中的某个字段来作为条件。

1、创建表，插入数据

CREATE TABLE `goods`  (
  `id` int(11) NULL DEFAULT NULL,
  `goods_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `price` double NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `goods` VALUES (300, '香蕉', 12);
INSERT INTO `goods` VALUES (200, '橘子', 24);
INSERT INTO `goods` VALUES (100, '香蕉', 12);
INSERT INTO `goods` VALUES (400, '苹果', 50);
INSERT INTO `goods` VALUES (500, '苹果', 70);

2、查询重复数据

select * from goods WHERE
	id IN  (
SELECT
	id 
FROM
	goods 
WHERE
	goods_name IN ( SELECT goods_name FROM `goods` GROUP BY goods_name HAVING COUNT( goods_name ) > 1 ))

3、查询重复数据，不包含最小的id的数据

SELECT
			id
		FROM
			goods 
		WHERE
		goods_name IN ( SELECT goods_name FROM `goods` GROUP BY goods_name HAVING COUNT( goods_name ) > 1 ) 
	AND id NOT IN ( SELECT min( id ) FROM goods GROUP BY goods_name HAVING COUNT( goods_name ) > 1 )

4、删除重复数据

以下sql语句会报错，原因：不能先查出需要更新或者删除表中的某个字段来作为条件。
DELETE 
FROM
	goods 
WHERE
	id IN 
	
			(SELECT
			id 
		FROM
			goods 
		WHERE
		goods_name IN ( SELECT goods_name FROM `goods` GROUP BY goods_name HAVING COUNT( goods_name ) > 1 ) 
	AND id NOT IN ( SELECT min( id ) FROM goods GROUP BY goods_name HAVING COUNT( goods_name ) > 1 ))

5、 解决办法：外面多嵌套一层查询，原先的查询作为子查询。

查询：
		SELECT
		a.id 
	FROM
		(
		SELECT
			id
		FROM
			goods 
		WHERE
		goods_name IN ( SELECT goods_name FROM `goods` GROUP BY goods_name HAVING COUNT( goods_name ) > 1 ) 
	AND id NOT IN ( SELECT min( id ) FROM goods GROUP BY goods_name HAVING COUNT( goods_name ) > 1 )
	) a

删除：
DELETE 
FROM
	goods 
WHERE
	id IN (
	SELECT
		a.id 
	FROM
		(
		SELECT
			id 
		FROM
			goods 
		WHERE
		goods_name IN ( SELECT goods_name FROM `goods` GROUP BY goods_name HAVING COUNT( goods_name ) > 1 ) 
	AND id NOT IN ( SELECT min( id ) FROM goods GROUP BY goods_name HAVING COUNT( goods_name ) > 1 )) a
	
	
	)