[RESOLVED] SQL - DELETE statement with JOINS in it

[the182guy]

Hi all,

I have these database tables in a MySQL database (version 5.0.67-community):

quotes (fields: id, customer_info_id, vehicle_id)
customer_info (fields: id, first_name, last_name, address_id)
addresses (fields: id)
vehicles (fields: id)

One quote = 1 record in each of those tables, all linked.

What I want to do is delete all records from all tables if the first or last name of the customer is 'test'. It seems too complicated to do it all in one query so I've tried doing one query per table like this:

DELETE FROM
  vehicles b
LEFT JOIN
  quotes a ON a.vehicle_id = b.id
LEFT JOIN
  customer_info c ON a.customer_info_id = c.id
WHERE
  c.first_name = 'test' OR
  c.last_name = 'test'

But the MySQL server gives a syntax error on that.

Any help is appreciated, cheers!

[Hack]

I think you need to look at Cascading Deletes.

Does MySQL support that?

[GaryMazzone]

What about using and In Statement for the delete?

[jmcilhinney]

DELETE statements don't contain joins. Only SELECT statements can contain joins. You can create a DELETE statement that contains a subquery in the WHERE clause. That subquery would be a SELECT statement and it can contain a join. That's basically what Gary is saying, e.g.

DELETE Child
WHERE ParentID IN
(
    SELECT Parent.ID
    FROM Parent
    INNER JOIN Grandparent
    ON Parent.GrandparentID = Grandparent.ID
    WHERE Grandparent.Name LIKE 'T%'
)

You delete every child whose grandparent's name starts with T.

[the182guy]

Solved it I think, does anyone see any problems with this query? It seems to be working as expected:

DELETE
  vehicles,
  customer_info,
  addresses,
  quotes
FROM
  quotes
LEFT JOIN
  vehicles ON vehicles.id = quotes.vehicle_id
LEFT JOIN
  customer_info ON customer_info.id = quotes.customer_info_id
LEFT JOIN
  addresses ON addresses.id = customer_info.address_id
WHERE
  customer_info.first_name = 'test' OR
  customer_info.last_name = 'test'

[the182guy]

Thanks jmcilhinney, GaryMazzone and Hack for the assistance. I might look into the IN statement some other time, the joins seem to be working for me.

[jmcilhinney]

Well I'll be b*ggered! I didn't even know that such a statement was possible. What database are you using?

[the182guy]

jmcilhinney, this is a MySQL database. You might be thinking of MS SQL Server?