Delete multiple rows in multiple table using INNER JOIN in mySQL 5 TABLES

1 Views

I want to run this query, but its not working. There 5 tables to delete. 

terms hav 1 row
term_taxonomy hav 1 row
term_relationships hav 1 row
post hav 5 rows
postmeta hav 5 rows

The structure follows wordpress nav_menu

Read More

I want to use that structure in my cms. Creating and retrieving done fine but I don’t know why the delete statement not working…

Query returns true, but rows are still there.

Here’s the query: 

$query = "DELETE t, tt, tr, p, m 
FROM terms AS t
INNER JOIN term_taxonomy AS tt ON t.term_id = tt.term_id
INNER JOIN term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN post AS p ON tr.object_id = p.ID
INNER JOIN postmeta AS m ON m.object_id = p.ID
WHERE t.term_id = 1;";
Post Views: 1

Related posts

Leave a Reply

1 comment

  1. At some point your INNER JOIN condition is ruling out all the records hence nothing is deleted. Here is how you should go about debugging something like this:

    Change the DELETE statement to a SELECT * FROM, records returned in a SELECT are the records that would be deleted if it was a DELETE statement.

    Start off with the most basic query, and slowly add your JOINs back in

    SELECT * FROM
FROM terms AS t
WHERE t.term_id = 1;

    Returns a record? Good. So add back in INNER JOIN term_taxonomy AS tt ON t.term_id = tt.term_id. Works again? Great, keep going until your query stops returning results (or the result set is clearly incorrect) . Once the results disappear, you know that either the last table you added in doesn’t have records to JOIN against, or more likely in this case your JOIN .. ON condition is incorrect.