[Resolved] SQL DELETE problem

[Erkilite]

I have two tables

donor (
id int unsigned not null auto_increment,
name varchar(50),
primary key (id)
);

donor_item (
id int unsigned not null auto_increment,
donor_id int unsigned not null,
title varchar(50),
primary key (id)
);

I want to delete all records from donor who do not have a matching record in donor_item.

Any ideas?

Thanks

[tsinka]

Hi,

you didn't specify the database server you use.

As of MySQL 4.x you can do this:

4.0.x:

DELETE donor FROM donor AS d LEFT JOIN donor_item AS di
ON d.id=di.donor_id
WHERE di.donor_id IS NULL

4.1.x:

DELETE d FROM donor AS d LEFT JOIN donor_item AS di
ON d.id=di.donor_id
WHERE di.donor_id IS NULL

MySQL supports any JOIN in DELETE queries.

Please test this on test tables ... I didn't test this myself.

Thomas