CODEWITHSUNDEEP
phpmysqlwordpressinner-joinsql-delete
user3034390
user3034390

Reputation: 99

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

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

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;";

Upvotes: 1

Views: 2035

Answers (1)

OGHaza
OGHaza

Reputation: 4795

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.

Upvotes: 2

Related Questions