Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Thanks. LEFT JOIN worked perfectly. –  hjpotter92 Apr 9 '13 at 1:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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