MYSQL record deletion

Question

Hi guys, make a few more changes on how to do the following:

Remove the beetle belonging to "A Wilks"

person (driver-id, name, address) car (license, model, year) accident (report-number, date, location) owns (driver-id, license) participated (driver-id, car, report-number, damage-amount) 

here is what i came up with:

 DELETE FROM car WHERE model = 'beetle' 

however, I know this is wrong because it will delete all bug cars, not just those owned by A Wilks.

Answer 1

 DELETE FROM car WHERE model = 'beetle' AND license = ( SELECT o.license from owns o INNER JOIN person p ON o.driver-id = p.driver-id WHERE p.name = 'A Wilks' ) 

Answer 2

 DELETE FROM car WHERE model = 'Beetle' AND license IN ( SELECT license FROM owns INNER JOIN Person ON owns.driver_id = person.driver_id WHERE person_name = 'A Wilks' 

You can use license = instead of license IN in the second line of the query if you are sure that there will be only one Beetle belonging to "A Wilks".

And please, consider sitting down with a copy of MySQL and trying some of the queries; You will learn the material better :)

Answer 3

 DELETE FROM car USING car INNER JOIN owns INNER JOIN person WHERE car.license=owns.license AND car.model='Beetle' AND owns.driver-id=person.driver-id AND person.name='A Wilks'; 

http://dev.mysql.com/doc/refman/5.0/en///delete.html

Answer 4

delete from car where model ='beetle' and licence=(select licence owns INNER JOIN Person ON owns.driver_id = person.driver_id WHERE person_name = 'A Wilks')

Answer 5

The answer given by Filippinev was good, and I would like to point out why this is a better method than the answers that use the subquery.

His answer uses join syntax (better performance and control):

 DELETE FROM car USING car INNER JOIN owns INNER JOIN person WHERE car.license=owns.license AND car.model='Beetle' AND owns.driver-id=person.driver-id AND person.name='A Wilks'; 

It uses syntax that executes a query with joined tables, and removes any matching records in the table from the DELETE FROM clause. An alternative approach uses a subquery format ( easy to code, worse performance ):

 DELETE FROM car WHERE model = 'beetle' AND license = ( SELECT o.license from owns o INNER JOIN person p ON o.driver-id = p.driver-id WHERE p.name = 'A Wilks' ) 

Subqueries are more expensive in performance than direct joins. If this were a query executed on a real Department of Automotive Vehicles system (with millions of records), you would like to avoid using subqueries at all costs and use a direct connection instead.

Answer 6

I suggest adding an owner to the car and doing:

 DELETE FROM car WHERE model = 'beetle' and owner = 'ownername' 

This will not work unless you include owner in car .