I'm working on a localhost XAMPP setup running MariaDB 10.3.16 and wrote the following query which deletes rows from multiple tables based on the first table's "ID" column. The problem is, it worked fine yesterday, and won't affect anything at all today on identical test data.
I've renamed my tables in this example, but everything else is the same. Assuming we're trying to delete multiple IDs 48, 49 and 50, which currently exist:
delete t1,t2,t3,t4,t5,t6,t7,t8 from table1 as t1
join table2 as t2 on t2.account_id = t1.id
join table3 as t3 on t3.account_id = t1.id
join table4 as t4 on t4.account_id = t1.id
join table5 as t5 on t5.account_id = t1.id
join table6 as t6 on t6.account_id = t1.id
join table7 as t7 on t7.account_id = t1.id
join table8 as t8 on t8.account_id = t1.id
where t1.id in (48,49,50);
I'm kinda pulling my hair out right now, because this query worked great multiple times yesterday as I was adding rows to test it on in my various tables. It worked every time. Today, I did some other work on a totally unrelated section of my project, and came back to using the DELETE endpoint of my API - only to discover this query simply will not delete anything any more. It goes through just fine, but it's now affecting 0 rows.
Copy & pasting the query directly into phpMyAdmin, where it also used to work, gives me the same result - 0 rows affected.
Is there something wrong with my query? Would you write this differently?
Is there any reason why the same query, in the same environment, working with the same test data, would work one day and not the next? A glitch maybe?
Thanks to Paul T. for putting me on the right path!
The issue with my query was that it only deleted anything if ALL of the tables contained the account_ids I was searching for. If any of them had no data for the associated ids, the DB would just stop and not affect anything.
I searched around a little more and found that changing JOIN in my query to LEFT JOIN allows the operation to complete in all tables where the ids were found, skipping over the ones that didn't have them. Time to read up some more on SQL!