使用LEFT JOIN删除

Question

我想根据引用了第一个表的另一个表上存在的数据从表中删除，但是，当我将其作为SELECT语句运行时，我具有可以正常工作并显示要删除的值的代码，但是当我将其更改为删除它给了我错误，我不明白为什么它们在那里。

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

Answer 1

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

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中customerID为：1）与8不同的所有记录2）不在状态为“ R”或“ H”的航班列表中

那不是你想做的吗？