从JOIN表中删除重复PostgreSQL

6ss1mwsb 于 2023-04-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(137)

我在PostgreSQL中有这样的查询

SELECT 
c.id,
c.firstname,
c.lastname
FROM
tblperson c
WHERE
NOT EXISTS(SELECT 1 FROM tblorder p WHERE p.id = c.id)
ORDER BY
id,
firstname,
lastname

as a result I got this
我如何删除第二个记录作为此POstgreSQL代码

DELETE FROM
tblperson dup_lang
USING tblperson dist_lang
WHERE dup_lang.id < dist_lang.id
AND dup_lang.firstname= dist_lang.firstname
AND dup_lang.lastname= dist_lang.lastname;

postgresql

来源：https://stackoverflow.com/questions/75891634/delete-duplicate-from-join-table-postgresql

1条答案

按热度按时间

pvabu6sv1#

假设您希望删除tblperson中与查询结果相匹配的两条记录，并基于此假设提供一个试探性的答案。

BEGIN;

DELETE FROM
   tblperson
WHERE
   id 
IN
(SELECT 
   c.id
FROM
   tblperson c
WHERE
    NOT EXISTS(SELECT 1 FROM tblorder p WHERE p.id = c.id)
ORDER BY
  id)
--Then either ROLLBACK; or COMMIT; depending on whether the query worked or not.

赞(0）回复(0）举报 2023-04-05

我来回答

从JOIN表中删除重复PostgreSQL

1条答案

相关问题

热门标签

最新问答