postgresql 在 Postgres 中使用左外连接删除

Delete using left outer join in Postgres

提问by dwlamb

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.

我正在将数据库从 MySQL 切换到 Postgres SQL。在 MySQL 中工作的选择查询在 Postgres 中工作，但类似的删除查询不工作。

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:

我有两个数据表，其中列出了某些备份文件所在的位置。现有数据 (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;

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;

I have tried DELETE edand 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 ed，DELETE ed.*两者都呈现syntax error at or near "ed"。如果我尝试不使用ed. 如果我尝试

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

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

Postgres 发回syntax error at or near "LEFT".

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

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

回答by Joshua Burgner

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 语句中使用 LEFT 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;

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

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

回答by Meow

Use the DELETE... USINGsyntax:

使用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;

回答by dwlamb

As bf2020 points out, postgres does not support JOINs when conducting a DELETE query. The proposed solution of a sub-query made me think of the solution. Refine the SELECT query from above and employ it as a sub-query to a DELETE query statement:

正如 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
);

Sub-queries can often be inefficient consuming time and CPU resources with some database systems, especially MySQL. From my experience I try to avoid using a sub-query due to that inefficiency plus that such queries are sometimes an easy way out to honing one's skill like learning JOIN syntax.

对于某些数据库系统，尤其是 MySQL，子查询通常会消耗时间和 CPU 资源，效率低下。根据我的经验，由于效率低下，我尽量避免使用子查询，而且此类查询有时是磨练自己的技能（如学习 JOIN 语法）的简单方法。

Since postgre does not permit delete queries using join, the above is the solution that works.

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

回答by bf2020

Instead of

代替

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;

please try

请尝试

DELETE FROM tv_episodes
WHERE cd_name = 'MediaLibraryDrive' AND 
  (tv_episodes.filename, tv_episodes.path IN
    (SELECT ed.filename, 
    ed.path 
    FROM tv_episodes AS ed 
    INNER JOIN data AS nd 
      ON ed.file_name = nd.file_name 
        AND ed.path = nd.path
    WHERE nd.cd_name IS NULL)
  )
  ;

JOINis not valid in a DELETEquery according to the postgresql documentation. You might need to concatenate the left and right parts of the IN expression.

JOINDELETE根据 postgresql 文档，在查询中无效。您可能需要连接 IN 表达式的左右部分。