SQL 使用 LEFT JOIN 删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15521954/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deleting using LEFT JOIN
提问by Matt
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.
我想根据引用第一个表的另一个表中存在的数据从表中删除，但是，当我将其作为 SELECT 语句运行时，我有可以工作并显示要删除的值的代码，但是当我将其更改为DELETE 它给了我错误，我不明白他们为什么在那里。
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:
错误：
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
I am using postgres
我正在使用 postgres
采纳答案by Laurent S.
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;
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'
它将删除 Leadcustomer 中的所有记录，其 customerID 为：1) 不同于 8 2) 不在状态为“R”或“H”的航班预订表中
Isn't that what you're trying to do ?
这不是你想要做的吗？
回答by user3048858
SAMPLE. DELETE RECORD IN TABLE 'A' IS THERE ARE NOT RECORD IN TABLE 'H'
样本。删除表“A”中的记录是否在表“H”中没有记录
DELETE A FROM ARTICULO_ALMACEN A
LEFT JOIN HISTORICO_UNION H
ON A.COD_ARTICULO = H.COD_ARTICULO
AND A.COD_ALMACEN = H.COD_ARTICULO_ALMACEN
AND A.TPROPIEDAD1 = H.PROPIEDAD1
AND A.TPROPIEDAD2 = H.PROPIEDAD2
AND A.TPROPIEDAD3 = H.PROPIEDAD3
WHERE H.COD_ARTICULO IS NULL
回答by Kyle
You will need to do this:
你需要这样做：
Delete from TableA where ID in (select ID from tableA a left outer join tableB b on a.ID = b.ID where b.ID is NULL)
Delete from TableA where ID in (select ID from tableA a left external join tableB b on a.ID = b.ID where b.ID is NULL)
回答by Rakesh
you can try this
你可以试试这个
DELETE leadCustomer FROM coursework.leadCustomer lc
LEFT JOIN coursework.flightBooking fb ON lc.customerID = fb.customerID
and status IN ('R','H')and fb.customer_id is not null
WHERE leadCustomer.customerID = 8;
回答by Ken Clark
Remove .*From leadCustomer.*ie:
.*从leadCustomer.*即删除：
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;
