CODEWITHSUNDEEP
mysqlsqlleft-joinsql-delete
Haseeb Ahmed
Haseeb Ahmed

Reputation: 11

DELETE all using Left join of 3 tables

SELECT * FROM property
LEFT JOIN installmentagreement
ON property.PId=installmentagreement.PId
LEFT JOIN installments
ON installmentagreement.Id=installments.Id
LEFT JOIN rentagreement
ON property.PId=rentagreement.PId
WHERE property.PId=39

This is the SELECT query that works as expected. I want to DELETE the result of this SELECT query. I used this query.

DELETE FROM property( SELECT * FROM property
LEFT JOIN installmentagreement
ON property.PId=installmentagreement.PId
LEFT JOIN installments
ON installmentagreement.Id=installments.Id
LEFT JOIN rentagreement
ON property.PId=rentagreement.PId
WHERE property.PId=39)

And this also.

DELETE FROM property
LEFT JOIN installmentagreement
ON property.PId=installmentagreement.PId
LEFT JOIN installments
ON installmentagreement.Id=installments.Id
LEFT JOIN rentagreement
ON property.PId=rentagreement.PId
WHERE property.PId=39

But it returns with Syntax Error. I want to covert the SELECT query into DELETE query. The DB I am using is MySQL.If there is any other better solution please suggest. Please help!

Upvotes: 1

Views: 1585

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

MySQL supports deleting from multiple tables in one statement, but you need to specify the tables you want to delete from:

DELETE p, ima, i, ra
    FROM property p LEFT JOIN
         installmentagreement ima
         ON p.PId = ima.PId LEFT JOIN
         installments i
         ON ima.Id = i.Id LEFT JOIN
         rentagreement ra
         ON p.PId = ra.PId
WHERE p.PId = 39;

Of course, you don't have to include all four tables after the DELETE, just the ones you want to delete from.

Upvotes: 0

Anand
Anand

Reputation: 1123

I would recommend using aliases for clarity and more:

SELECT *
FROM property as p
LEFT JOIN installmentagreement as ia
ON p.PId=ia.PId
LEFT JOIN installments as i
ON ia.Id=i.Id
LEFT JOIN rentagreement as ra
ON p.PId=ra.PId
WHERE p.PId=39

Then you can convert easily to delete statement as follows:

delete p
FROM property as p
LEFT JOIN installmentagreement as ia
ON p.PId=ia.PId
LEFT JOIN installments as i
ON ia.Id=i.Id
LEFT JOIN rentagreement as ra
ON p.PId=ra.PId
WHERE p.PId=39

And if you wanted to delete records from some of the left-joined tables before you delete the property records, you can just as easily change the alias in the delete statement:

delete ra
FROM property as p
LEFT JOIN installmentagreement as ia
ON p.PId=ia.PId
LEFT JOIN installments as i
ON ia.Id=i.Id
LEFT JOIN rentagreement as ra
ON p.PId=ra.PId
WHERE p.PId=39

Upvotes: 1

Related Questions