MySQL delete from Multiple tables using ON not working

Question

Hey everyone I am trying to delete from multiple tables with the following query

mysql>         DELETE
    ->           info, pagelets, shingles, links
    ->         FROM
    ->           info
    ->         INNER JOIN pagelets
    ->           ON info.page_key=144
    ->           AND info.page_key=pagelets.page_key
    ->         INNER JOIN shingles
    ->           ON pagelets.pagelet_serial=shingles.pagelet_serial
    ->         INNER JOIN links
    ->           ON pagelets.pagelet_serial=links.pagelet_serial
    -> ;
Query OK, 2050 rows affected (0.08 sec)

mysql> SELECT * FROM info;
+--------+----------+
| netloc | page_key |
+--------+----------+
|      1 |        2 |
|      1 |      118 |
+--------+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM pagelets WHERE page_key =144;
+----------+----------------+
| page_key | pagelet_serial |
+----------+----------------+
|      144 |            245 |
|      144 |            246 |
|      144 |            249 |
|      144 |            253 |

Unfortunately, this only works on a the table links and info. ( Don' tknow why it doens't work on pagelets and shingles)

The original query was:

#        DELETE 
#          info, pagelets, shingles, links
#        FROM
#          info INNER JOIN pagelets INNER JOIN shingles INNER JOIN links
#        WHERE
#          info.page_key=%(page_key)s
#          AND info.page_key=pagelets.page_key
#          AND pagelets.pagelet_serial=shingles.pagelet_serial
#          AND pagelets.pagelet_serial=links.pagelet_serial
#

any one can explain this mystery?

Answer 1

Hi,

Replace your table names listed to delete into this :

DELETE info.*, pagelets.*, shingles.*, links.*

Answer 2

A cleaner alternative would be to use foreign key restrictions with ON DELETE CASCADE in these dependent tables (pagelets, shingles, links) so that the delete statement doesn't need to be so complicated.

Answer 3

Try this query as an experiment:

SELECT * FROM info
INNER JOIN pagelets ON (info.page_key = pagelets.page_key)
LEFT JOIN shingles ON (pagelets.pagelet_serial = shingles.pagelet_serial)
LEFT JOIN links ON (pagelets.pagelet_serial = links.pagelet_serial)
WHERE info.page_key = 144;

You'll probably see NULL in either the shingles and links columns, and no overlap between these. That is, there's no row in pagelets that joins to both links and shingles. So if you change to an INNER JOIN:

SELECT * FROM info
INNER JOIN pagelets ON (info.page_key = pagelets.page_key)
INNER JOIN shingles ON (pagelets.pagelet_serial = shingles.pagelet_serial)
INNER JOIN links ON (pagelets.pagelet_serial = links.pagelet_serial)
WHERE info.page_key = 144;

You'll get an empty set.

When you do a DELETE of course it deletes zero rows because the joins result in an empty set.

As for your question about why does this delete from two tables but not the others, I have to question this. I think you made a mistake, because I can't see how it could do that.