MYSQL Delete FROM All Tables Where Condition TRUE-CodePudding

I have at least 30 MySQL Tables with full of data. I have a MySQL event, which should delete all rows from all tables where the *.uid = (SELECT uid FROM 'cdelete' WHERE DATEDIFF( NOW( ) , date ) >=1) -> The result should be 1, because only the customers with the id 1 is in the delete table. So I want to remove every rows where the uid = 1

What I've tried so far:

DELETE cdelete, customers, orders 
FROM cdelete 
INNER JOIN customers ON customers.id = cdelete.uid
INNER JOIN orders ON orders.uid = cdelete.uid
WHERE cdelete.uid = (SELECT uid FROM 'cdelete' WHERE DATEDIFF( NOW( ) ,  date ) >=1)

It seems that this kind of deletion is impossible, or I made some mistake.. Any idea how to delete from all tables where the uid is retrived from the SELECT uid FROM 'cdelete' WHERE DATEDIFF( NOW( ) , date ) >=1) statement?

CodePudding user response：

You don't need the subquery, just test the date directly in the WHERE condition.

DELETE cdelete, customers, orders 
FROM cdelete 
LEFT JOIN customers ON customers.id = cdelete.uid
LEFT JOIN orders ON orders.uid = cdelete.uid
WHERE DATEDIFF( NOW( ) ,  cdelete.date ) >=1

You need to use LEFT JOIN rather than INNER JOIN in case there are no related rows in the other tables.

Also, if you configure customers and orders with a foreign key and ON DELETE CASCADE then you only need to delete from cdelete -- the related rows in the other tables will be deleted automatically.