我有一个表,其中有具有以下属性的重复记录:
- Primary_ID
- 评论
现在的问题是存在具有相同主键但不同"注释"的重复记录。记录如下:
+------------+-----------+
| Primary_ID | Comments |
+------------+-----------+
| 1 | abc |
| 1 | null |
| 1 | abc |
| 2 | asdf |
| 3 | null |
| 4 | abc |
| 4 | null |
+------------+-----------+
我希望查询删除具有重复项且注释为空的记录。下面是它应该看起来像的样子:
+------------+----------+
| Primary_ID | Comments |
+------------+----------+
| 1 | abc |
| 2 | asdf |
| 3 | null |
| 4 | abc |
+------------+----------+
我尝试使用以下查询完成此操作:
delete from myTab
where PRIMARY_ID
not in
( SELECT distinct PRIMARY_ID, COMMENT
FROM myTab)
DELETE FROM myTab
WHERE Comments IS NULL
OR PRIMARY_ID NOT IN(SELECT PRIMARY_ID
FROM myTab
HAVING COUNT(PRIMARY_ID)>1)
编辑:
将OR
替换为AND
,因为它取决于您所说的删除具有重复项且注释为空的记录的含义