MYSQL删除重复记录SQL

最新推荐文章于 2021-02-18 21:00:10 发布

fairyhawk

最新推荐文章于 2021-02-18 21:00:10 发布

阅读量1.2k

点赞数

分类专栏： MySQL学习笔记

本文链接：https://blog.csdn.net/fairyhawk/article/details/9272495

版权

MySQL学习笔记专栏收录该内容

8 篇文章 0 订阅

订阅专栏

表letter_detail

字段id,book_chapter_id,word

其中2个字段book_chapter_id,word有重复的需要删除掉，只保留一条

查询重复的：

SELECT MIN(id),COUNT(1) aa  
FROM letter_detail
GROUP BY letter_detail.book_chapter_id,letter_detail.word
HAVING aa>1

写了个SQL删除结果报错，不能在更新的时候同时检索这个表

Error Code : 1093
You can't specify target table 'letter_detail' for update in FROM clause

DELETE FROM letter_detail WHERE (letter_detail.book_id,letter_detail.book_chapter_id,letter_detail.word )
IN(SELECT ld.book_id,ld.book_chapter_id,ld.word
FROM letter_detail ld
GROUP BY ld.book_id,ld.book_chapter_id,ld.word
HAVING COUNT(1)>1)
AND letter_detail.id NOT IN(SELECT MIN(id)
FROM letter_detail ld
GROUP BY ld.book_id,ld.book_chapter_id,ld.word
HAVING COUNT(1)>1)

重新改下：

查询SQL：

SELECT t1.id,t1.word  FROM letter_detail t1
,  (SELECT ld.book_chapter_id,ld.word,MIN(id) id
	FROM letter_detail ld
	GROUP BY ld.book_id,ld.book_chapter_id,ld.word
	HAVING COUNT(1)>1 
) AS minld
WHERE t1.book_id=3 AND t1.word=minld.word AND t1.id !=minld.id

删除SQL（将上面的查询作为子表）：

DELETE ma FROM letter_detail ma, 
(
SELECT t1.id,t1.word  FROM letter_detail t1
,  (SELECT ld.book_chapter_id,ld.word,MIN(id) id
	FROM letter_detail ld
	GROUP BY ld.book_id,ld.book_chapter_id,ld.word
	HAVING COUNT(1)>1 
) AS minld
WHERE t1.book_id=3 AND t1.word=minld.word AND t1.id !=minld.id ) mb WHERE ma.book_id=3 AND ma.id=mb.id

我的多写了个book_id=3的限定条件。至此搞定。

不会再报该死的You can't specify target table '*****l' for update in FROM clause了。