在Postgres中使用左外连接删除

Question

我正在将数据库从MySQL切换到Postgres SQL。在MySQL中有效的select查询在Postgres中也有效，但类似的delete查询却不能。

我有两个数据表，列出了某些备份文件的位置。现有数据(ed)和新数据(nd)。此语法将挑选出可能说明文件在现有数据表中的位置的现有数据，根据相等的文件名和路径对其进行匹配，但没有关于文件在新数据中的位置的信息:

SELECT ed.id, ed.file_name, ed.cd_name, ed.path, nd.cd_name
FROM tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND 
ed.path = nd.path
WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

我希望使用以下语法运行删除查询：

DELETE ed
FROM tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND 
ed.path = nd.path
WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

我试过< code>DELETE ed和< code>DELETE ed。*这两种情况都会在“ed”处或附近呈现< code >语法错误。如果我在没有别名< code>ed的情况下尝试，也会出现类似的错误。如果我尝试

DELETE FROM tv_episodes AS ed
LEFT  JOIN data AS nd.....

Postgres在“LEFT”处或附近发送回语法错误。

我被难住了，在使用特定于psql的连接的删除查询上找不到太多东西。




Answer 1

使用DELETE… USING语法：

DELETE FROM tv_episodes USING data WHERE 
tv_episodes.file_name = data.file_name AND 
tv_episodes.path = data.path AND 
tv_episodes.cd_name = 'MediaLibraryDrive' AND 
data.cd_name IS NULL;

Answer 2

正如 bf2020 指出的那样，postgres 在执行 DELETE 查询时不支持 JOIN。子查询的建议解决方案让我想到了解决方案。从上面优化 SELECT 查询，并将其用作 DELETE 查询语句的子查询：

DELETE FROM tv_episodes 
WHERE id in (
    SELECT ed.id
    FROM tv_episodes AS ed
    LEFT OUTER JOIN data AS nd ON
    ed.file_name = nd.file_name AND 
    ed.path = nd.path
    WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL
);

对于一些数据库系统，特别是MySQL，子查询通常是低效的，会消耗时间和CPU资源。根据我的经验，我尽量避免使用子查询，因为这样效率很低，而且这种查询有时是磨练技能的简单方法，比如学习连接语法。

由于postgre不允许使用连接删除查询，因此上述解决方案是有效的。

Answer 3

正如其他人所指出的，您不能直接在DELETE语句中离开JOIN。但是，您可以使用USING语句对目标表的主键进行自连接，然后对自连接表进行左连接。

DELETE FROM tv_episodes
USING tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
   ed.file_name = nd.file_name AND 
   ed.path = nd.path
WHERE
   tv_episodes.id = ed.id AND
   ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

注意tv_epinodes上的自连接。WHERE子句中的id。这避免了上面提供的子查询路由。