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

Question

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

INNER JOIN postmeta AS m ON tr.object_id = p.ID shouldn't this be INNER JOIN postmeta AS m ON m.object_id = p.ID ? — Bill Karwin
– Bill Karwin, Commented Nov 25, 2013 at 23:59
I have corrected this mistake and ran the query... still faild :( — user3034390
– user3034390, Commented Nov 26, 2013 at 0:03
If you change the top line from DELETE .... to SELECT * FROM you can see if you would be deleting anything with the query - which obviously you aren't, but then you can work back from there. — OGHaza
– OGHaza, Commented Nov 26, 2013 at 0:03

OGHaza · Accepted Answer · 2013-11-26 00:11:31Z

2

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.

answered Nov 26, 2013 at 0:11

OGHaza

4,7957 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bill Karwin Over a year ago

+1 I was writing a similar answer. Good advice to decompose the problem. Note you can also use OUTER JOIN in the DELETE for tables that might not have matching rows.

user3034390 Over a year ago

Thanks for the great idea. I am going to follow the instructions. and let you know. thanks OGHaza

user3034390 Over a year ago

there wat two mistakes in the query. post table was posts and column in postmeta was post_id instead object_id

user3034390 Over a year ago

"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 posts AS p ON tr.object_id = p.ID INNER JOIN postmeta AS m ON m.post_id = p.ID WHERE t.term_id = 1;"

OGHaza Over a year ago

@user3034390 Managed to fix it then?

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related