在我的数据库中,我有大约67000行,但我有12k个具有不同键的重复行。
我想删除重复项并只保存一行。
表名: seb_societe
相似的列: nom,电话,地址,邮编
我试过了,但不起作用:
DELETE
FROM seb_societe AS T
WHERE T.rowid < ANY (SELECT rowid
FROM seb_societe AS T2
WHERE T.rowid <> T2.rowid
AND T.nom = T2.nom
AND T.address = T2.address);有谁能告诉我为什么我的尝试没有成功,或者如何达到我想要的结果。
发布于 2017-04-07 21:56:10
delete
from seb_societe
where exists
(
select *
from
(
select 1
from seb_societe t2
where t2.row_id > row_id
and nom = T2.nom
and address = T2.address
) x1
)发布于 2017-04-07 22:01:26
您可以使用delete on join:
delete s
from seb_societe s
join (
select nom,
address,
max(rowid) as max_rowid
from seb_societe
group by nom,
address
) t on s.nom = t.nom
and s.address = t.address
and s.rowid <> t.max_rowid;发布于 2017-04-07 22:04:16
DELETE t1 FROM
seb_societe t1 LEFT JOIN seb_societe t2 ON
t1.nom = t2.nom AND t1.address = t2.address
WHERE t1.row_id < t2.row_id;先试一试备份...
这应该会留下重复项中具有最高row_id的行。
https://stackoverflow.com/questions/43279902
复制相似问题