[SOLVED] 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!

[Maq]

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!

 

That IS only 1 query...

 

Why are you SELECTING and JOINING if you're trying to DELETE from all these tables?

 

You need something like:

 

DELETE FROM vehicles WHERE first_name = 'test' OR last_name = 'test';
DELETE FROM quotes WHERE first_name = 'test' OR last_name = 'test';

 

OR (I think this is correct syntax):

 

DELETE vehicle.*, quotes.* FROM vehicle, quotes WHERE vehicle.first_name = 'test' OR vehicle.last_name = 'test';

[the182guy]

Maq, thanks for your response...

 

The SELECT was my mistake, I pasted the wrong query into the post, I have editted it since then to DELETE.

 

Those examples provided won't work, there is no first_name or last_name fields on the vehicle table.

 

first_name and last_name only exist in the customer_info table.

 

You are correct that the SQL I posted is only one query, which I did explain above it. It's not a problem to do one query per table like my example shows the query for the vehicles table. It would only be four queries and this script will only run once daily.

[Maq]

DELETE vehicle.*, quotes.* FROM vehicle, quotes WHERE quotes.first_name = 'test' OR quotes.last_name = 'test';

[the182guy]

Thanks maq, I solved it with this query:

 

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'