Deleting using LEFT JOIN

Question

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:

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

use `delete from table where id in (your select query)` – jonasnas Mar 20 '13 at 11:22 — jonasnas, Mar 20 '13 at 11:22
Remove the .* and put on DELETE loadCustomer FROM .... – Rodolfo Faquin Mar 20 '13 at 11:22 — Rodolfo Faquin, Mar 20 '13 at 11:22
Try this: "DELETE leadCustomer FROM ..." – Arvo Mar 20 '13 at 11:22 — Arvo, Mar 20 '13 at 11:22

score 87 · Answer 1 · answered Nov 25 '14 at 12:22

87

SAMPLE. DELETE RECORD IN TABLE 'A' IS THERE ARE NOT RECORD IN TABLE '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

answered Nov 25 '14 at 12:22

user3048858

1,036
1
8
5

6

this works, and it's the correct answer for the title – Omu May 27 '15 at 10:34
8

Question is tagged postgresql but LEFT JOIN is not valid with DELETE in postgresql. – user9645 Dec 20 '16 at 12:46
This one is useful for when there are more tan one column in the join condition. The "not in" does not work in those cases. "where not exists(select...)" caould also be used, but i think this answer is more elegant. – Jahaziel Mar 11 '20 at 15:52
ERROR: syntax error at or near "A" LINE 1: DELETE A FROM ARTICULO_ALMACEN A – abbas Jul 14 '20 at 13:01
5

This does NOT work in postgres. Why does this have so many upvotes? – abarazal Apr 02 '21 at 23:31

score 23 · Accepted Answer · answered Mar 20 '13 at 11:22

23

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'

Isn't that what you're trying to do ?

answered Mar 20 '13 at 11:22

Laurent S.

6,816
2
28
40

You were right, I didn't need a join. and the neatening of that middle row also worked, however what it does is given an ID, it deletes that row if it has no related bookings with status R or H – Matt Mar 20 '13 at 11:34
2

This works however NOT IN, won't use the index of customerID (if you have one) which can be a pain (Check Comments in this one http://stackoverflow.com/questions/652770/delete-with-join-in-mysql) – zzarbi Feb 15 '14 at 02:31
2

This is a very old question but nobody has mentioned it in an answer (maybe PostgreSQL didn't offer at the time) but NOT EXISTS is a better option to emulate the left join than NOT IN if you're looking for performance. – jkratz Oct 20 '18 at 14:51

score 2 · Answer 3 · answered Feb 13 '14 at 22:22

2

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)

answered Feb 13 '14 at 22:22

Kyle

21
1

score -4 · Answer 4 · answered Mar 20 '13 at 11:22

-4

Remove .* From leadCustomer.* ie:

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;

answered Mar 20 '13 at 11:22

Ken Clark

2,500
15
15

1

PostgreSQL does not support a `LEFT JOIN` that refers to `DELETE`. – Skrol29 Jul 09 '14 at 13:06

score -5 · Answer 5 · answered Mar 20 '13 at 11:35

-5

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;

answered Mar 20 '13 at 11:35

Rakesh

289
1
3
8

I believe, based on the [documentation](http://www.postgresql.org/docs/9.3/static/sql-delete.html), that the `LEFT JOIN` here will cause a syntax error. – Jared Beck Apr 15 '14 at 00:40
1

PostgreSQL does not support a `LEFT JOIN` that refers to `DELETE`. – Skrol29 Jul 09 '14 at 13:07

Deleting using LEFT JOIN

5 Answers5

Linked