在Postgres中使用左外部联接删除 [英] Delete using left outer join in Postgres

问题描述

我正在将数据库从MySQL切换到Postgres SQL。在MySQL中可以使用的选择查询在Postgres中可以使用，但在删除查询中不能使用。

I am switching a database from MySQL to Postgres SQL. A select query that worked in MySQL works in Postgres but a similar delete query does not.

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

I have two tables of data which list where certain back-up files are located. Existing data (ed) and new data (nd). This syntax will pick out existing data which might state where a file is located in the existing data table, matching it against equal filename and path, but no information as to where it is located in the new data:

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;

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

I wish to run a delete query using this syntax:

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;

我尝试了 Delete ed 和 Delete ed。* 都在 ed 或附近呈现语法错误。如果我尝试不使用 ed 的别名，则会出现类似的错误。如果我尝试

I have tried DELETE ed and DELETE ed.* both of which render syntax error at or near "ed". Similar errors if I try without the alias of ed. If I attempt

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

Postgres在左或附近发送语法错误。

Postgres sends back syntax error at or near "LEFT".

我很困惑，在使用特定于psql的联接的删除查询中找不到很多东西。

I'm stumped and can't find much on delete queries using joins specific to psql.

推荐答案

正如其他人指出的那样，您不能直接在DELETE语句中左移JOIN。但是，您可以使用USING语句在主键上自动联接到目标表，然后对该联接表进行左联接。

As others have noted, you can't LEFT JOIN directly in a DELETE statement. You can, however, self join on a primary key to the target table with a USING statement, then left join against that self-joined table.

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;

请注意WHERE子句中tv_episodes.id上的自连接。这样可以避免上面提供的子查询路由。

Note the self join on tv_episodes.id in the WHERE clause. This avoids the sub-query route provided above.

这篇关于在Postgres中使用左外部联接删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！