如何使用外部联接删除行 [英] How to delete rows using an outer join

问题描述

使用LEFT JOIN从PostgreSQL表中删除记录时遇到问题.

I've got a problem to delete records from a PostgreSQL table, using a LEFT JOIN.

我想删除通过以下查询获得的行:

I'd like to delete rows I get with the following query:

SELECT * FROM url 
LEFT JOIN link_type ON url.link_type = link_type.id 
WHERE link_type.id IS NULL

要这样做，这是我所做的:

To do so, here is what I did:

 DELETE FROM url
 USING link_type
 WHERE url.link_type = link_type.id  AND link_type.id IS NULL

查询有效，但不会删除任何内容，尽管这正是文档中所解释的内容: http://www.postgresql.org/docs/current/static/sql-delete.html .

Query works but doesn't delete anything, although that's exactly what's explained in the doc: http://www.postgresql.org/docs/current/static/sql-delete.html.

我的问题是由于查询中的IS NULL还是我缺少了什么?

Is my problem due to IS NULL in the query or Am I missing something?

推荐答案

仍然不理解为什么我之前的查询不起作用(如果有人可以解释，那会很好)，但这是我的诀窍:

Still don't understand why my previous query doesn't work (if someone could explain, would be nice), but here is how I did the trick:

DELETE FROM url WHERE NOT EXISTS (SELECT * FROM link_type WHERE url.link_type = link_type.id );

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