0

I have 2 tables, car and owners. I car has 0 to many owners. An owner always owns a car. I want to delete all cars that were manufactured before 2022-01-01. Consequently, deleting owners that owned cars that were manufactured before 2022-01-01.

CREATE TABLE car (
 car_id INT primary key,
 manufacture_date datetime not null
);

CREATE TABLE owners (
  owner_id  INT primary key,
  car_id_owned INT NOT NULL,
  owner_name varchar(32) not null,
  FOREIGN KEY (car_id_owned) REFERENCES car(car_id)
);

I can easily do that with DELETE FROM cars WHERE manufacture_date < '2022-01-01';

However, if I do this, it means that an owner may end up owning a car that doesn't exist.

If I do the following select statement, it returns a table that I want to delete:

SELECT *
FROM car c
LEFT JOIN owners o ON c.car_id = o.car_id_owned
WHERE c.manufacture_date < '2022-01-01'

How can I delete all the owners of cars that were manufactured before 2022-01-01. And delete all the cars that were manufactured before 2022-01-01?

Zoey Malkov
  • 776
  • 6
  • 16
  • Check for cascade delete , that would help you in removing associations between parent and child table simultaneously – thar45 Apr 28 '22 at 09:36
  • @Stu Not really. I understand that I need to delete owners first. But I don't get how to delete owners. – Zoey Malkov Apr 28 '22 at 09:37
  • What about car owners having several cars, manufactured both before and after date? – jarlh Apr 28 '22 at 09:40
  • @jarlh To keep the question simple, I am just assuming one owner, only owns one car. So there will only be 1 `Bob` and 1 `Bill`. – Zoey Malkov Apr 28 '22 at 09:44

0 Answers0