Delete entry from related tables

I can not use InnoDB engine because of some dependency troubles in LuaSQL and PtokaX.

My current query is something like:

DELETE e.*, m.* 
FROM entries e 
INNER JOIN magnets m 
    ON m.eid = e.id
WHERE e.id = %d

where, %d is an integer passed by the user. This query works if there is atleast one entry in the magnets table related to the entries table.

But, it fails when the entries has no dependent row in magnets table. Can I modify this query to perform deletion from both tables, independent of child-rows in magnets? Or do I need to first check if a child exists and delete accordingly?

There are several things you could try:

DELETE Style #1 : LEFT JOIN

DELETE e.*, m.* 
FROM `entries` e 
LEFT JOIN `magnets` m 
    ON m.`eid` = e.`id` 
WHERE e.`id` = %d

DELETE Style #2 : Two Independent DELETE queries

(without FOREIGN KEY defined)

DELETE FROM entries WHERE id = %d;
DELETE FROM magnets WHERE eid = %d;

DELETE Style #2b : Two DELETE queries : the order matters

(when there is a FOREIGN KEY defined between the two tables

with the ON DELETE NO ACTION property)

DELETE FROM magnets WHERE eid = %d;
DELETE FROM entries WHERE id = %d;

DELETE Style #3 : One DELETE query

(when there is a FOREIGN KEY defined

with the ON DELETE CASCADE property)

DELETE FROM entries WHERE id = %d;

Give it a Try !!!

StackExchange Database Administrators Q#39536, answer score: 4