使用LEFT JOIN删除 [英] Deleting using LEFT JOIN

问题描述

我想根据另一个引用第一个表的表上存在的数据从表中删除，但是，当我将其作为SELECT语句运行时，我有能正常工作并显示要删除的值的代码，但是当我将其更改为DELETE会给我带来错误，我不知道为什么会出现这些错误.

I want to delete from a table depending on data that exists on another table that references the first, however, I have the code that works and shows the value to be deleted when I run it as a SELECT stetement, however when I change that to DELETE it gives me errors, that I don't understand why they're there.

DELETE leadCustomer.* FROM coursework.leadCustomer LEFT JOIN coursework.flightBooking
ON leadCustomer.customerID = flightBooking.customerID
WHERE leadCustomer.customerID NOT IN (
SELECT customerID FROM (SELECT customerID, status FROM coursework.flightBooking) AS
StatusCount where status IN  ('R','H') GROUP BY customerID
)
AND leadCustomer.customerID = 8;

错误:

ERROR:  syntax error at or near "leadCustomer"
LINE 1: DELETE leadCustomer.* FROM coursework.leadCustomer LEFT JOIN...
               ^

********** Error **********

ERROR: syntax error at or near "leadCustomer"
SQL state: 42601
Character: 8

我正在使用postgres

I am using postgres

推荐答案

从我看到的地方，您实际上不需要联接即可执行此操作...

From where I see it, you don't actually need a join to perform this...

DELETE FROM coursework.leadCustomer 
WHERE leadCustomer.customerID NOT IN (
SELECT distinct customerID FROM coursework.flightBooking  where status IN  ('R','H') 
)
AND leadCustomer.customerID = 8;

它将删除leadcustomer中具有以下客户ID的所有记录: 1)与8不同 2)不在状态为"R"或"H"的飞机机上预订

it will delete all records in leadcustomer with a customerID that is : 1) different from 8 2) Not in table flightbooking with status 'R' or 'H'

这不是您要做什么吗?

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