John Davidson

Message:

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?

  
  

---

My Answer:
There could be a few reasons why your DELETE query is not affecting any rows today, even though it worked fine yesterday. Here are a few things you can check:

1. Data Integrity: Make sure that the data you are trying to delete actually exists in the tables. Double-check that the IDs 48, 49, and 50 are present in the "table1" before running the DELETE query.

2. Foreign Key Constraints: Check if there are any foreign key constraints set up on the tables that might be preventing the deletion of rows. If there are constraints, make sure that deleting rows from "table1" does not violate any constraints in the other tables.

3. Transactions: If you are running the DELETE query within a transaction, make sure that the transaction is committed properly. If the transaction is not committed, the changes will not be reflected in the database.

4. Permissions: Ensure that the user executing the DELETE query has the necessary permissions to delete rows from the tables.

5. Server Restart: Sometimes, restarting the database server (in your case, MariaDB) can resolve unexpected behavior. Try restarting the server and running the query again.

If none of the above suggestions help, you may want to try running the query with a single table join at a time to see if any specific join is causing the issue. Additionally, you can try adding a SELECT statement with the same conditions to see if it returns the expected rows before attempting the DELETE operation.

If the issue persists, there might be some other underlying problem with your database or environment that needs further investigation.