mysql删除“delete……from……exists”不执行

[iPaperMan]

DELETE m FROM `mall_commodity_launch_codev2` m
WHERE EXISTS (
SELECT sCode, nCmdtId FROM (
SELECT t.`s_code` AS sCode, t.`n_commodity_id` AS nCmdtId, COUNT(1) NUM 
FROM  `mall_commodity_launch_codev2` t
 WHERE 1=1 GROUP BY t.`n_commodity_id`,t.`s_code` HAVING NUM > 1) tt WHERE tt.sCode = m.`s_code` AND tt.nCmdtId = m.`n_commodity_id`)

在sqlyog中执行，不报错，但是也没有删除效果：没有删掉数据，一直显示执行中……

[iPaperMan]

引用 1 楼 yupeigu 的回复:

mysql的delete语句好像是不支持子查询的。 不过，你可以修改成 关联查询，类似于：

DELETE m FROM `mall_commodity_launch_codev2` m,
(
SELECT sCode, nCmdtId FROM (
SELECT t.`s_code` AS sCode, t.`n_commodity_id` AS nCmdtId, COUNT(1) NUM 
FROM  `mall_commodity_launch_codev2` t
 WHERE 1=1 GROUP BY t.`n_commodity_id`,t.`s_code` HAVING NUM > 1) tt
WHERE tt.sCode = m.`s_code` AND tt.nCmdtId = m.`n_commodity_id`

用关联查询果然是可以的

DELETE m FROM `mall_commodity_launch_codev2` m,
(
SELECT t.`n_id` AS nId, t.`s_code` AS sCode, COUNT(1) NUM 
FROM  `mall_commodity_launch_codev2` t WHERE 1=1 GROUP BY t.`n_commodity_id`,t.`s_code` HAVING NUM > 1) tt
WHERE tt.sCode = m.`s_code` AND tt.nId = m.`n_id`

但是我的目的是过滤数据库中重复的数据，当NUM为2条以上时，就要执行多次才能将重复记录完全删除，有没有可以执行一次就达到想要结果的方法？ 我想要的结果就是执行完后

SELECT t.`n_id` AS nId, t.`s_code` AS sCode, COUNT(1) NUM 
FROM  `mall_commodity_launch_codev2` t WHERE 1=1 GROUP BY t.`n_commodity_id`,t.`s_code` HAVING NUM > 1

执行结果为空 mysql的delete语句应该是支持子查询的，我想了一下，可能是因为表中数据量太大（数十万条），exists子查询数据量也很大，导致执行效率太低；在子查询加了limit 2000后是可以执行的

DELETE m FROM `mall_commodity_launch_codev2` m
WHERE EXISTS (
SELECT sCode, nId FROM (
SELECT t.`n_id` AS nId, t.`s_code` AS sCode, COUNT(1) NUM 
FROM  `mall_commodity_launch_codev2` t WHERE 1=1 GROUP BY t.`n_commodity_id`,t.`s_code` HAVING NUM > 1 limit 2000) tt
 WHERE tt.sCode = m.`s_code` AND tt.nId = m.`n_id`)

[LongRui888]

mysql的delete语句好像是不支持子查询的。 不过，你可以修改成 关联查询，类似于：

DELETE m FROM `mall_commodity_launch_codev2` m,
(
SELECT sCode, nCmdtId FROM (
SELECT t.`s_code` AS sCode, t.`n_commodity_id` AS nCmdtId, COUNT(1) NUM 
FROM  `mall_commodity_launch_codev2` t
 WHERE 1=1 GROUP BY t.`n_commodity_id`,t.`s_code` HAVING NUM > 1) tt
WHERE tt.sCode = m.`s_code` AND tt.nCmdtId = m.`n_commodity_id`