travelboy travelboy -4 years ago 106
MySQL Question

Delete from one or two tables in one query

I have two MySQL tables:

programs versions
--------- ------------------------
id | name id | program_id | data
---+----- ---+------------+-------
1 | One 1 | 1 | OneFoo
2 | Two 2 | 1 | OneBar
3 | 2 | TwoBaz


versions.program_id
is a foreign key. When deleting a version, I also want to delete the associated program only if the version being deleted was the last one with that program_id. For example, deleting version 1 should not delete any program, but deleting version 3 should also delete program 2.

I am doing this and it works:

DELETE FROM versions WHERE id = ?;

DELETE p FROM programs p
WHERE p.id = ?
AND NOT EXISTS (
SELECT 1 FROM versions v WHERE v.programId = p.id
);


Is there a more efficient way to do this with just one query? Or by using triggers?

Answer Source

Use trigger:

delimiter //
create trigger del
AFTER DELETE ON versions FOR EACH ROW
begin
     DELETE FROM programs WHERE  
     programs.id = old.program_id 
     AND 
     NOT EXISTS (SELECT 1 FROM versions WHERE versions.program_id = old.program_id);
end
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download