3

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?

2 Answers 2

4

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 !!!

0
0

DELETE CASCADE: When we create a foreign key using this option, it deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key.

Actually you can make your life easier by setting the relationship that connects "magnets" and "entries" tables to CASCADE options.

In this way the deletion in the child table will be done automatically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.