CODEWITHSUNDEEP
sqlpostgresqljoinleft-join
Matt
Matt

Reputation: 1521

Deleting using LEFT JOIN

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

Upvotes: 41

Views: 114477

Answers (5)

user3048858
user3048858

Reputation: 1046

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

Upvotes: 88

Kyle
Kyle

Reputation: 21

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)

Upvotes: 2

Rakesh
Rakesh

Reputation: 299

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;

Upvotes: -5

Laurent S.
Laurent S.

Reputation: 6946

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 ?

Upvotes: 25

Ken Clark
Ken Clark

Reputation: 2530

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;

Upvotes: -4

Related Questions