There are two tables.

id| name | date 1| item1| 2013-10-06 08:11:25 2| item2| 2013-03-06 08:11:25 3| item3| 2013-06-04 08:11:25

and

 id|id_s | name | date 1| 2 | name1| 2013-03-06 08:11:25 2| 1 | name2| 2013-10-06 08:11:25 3| 2 | name2| 2013-01-02 08:11:25

It all works like this:
The second table is taken, the first LEFT JOIN is added and something is output. (The second table's id_s points to the first id)

How to delete from the FIRST table those records that are not referenced from the second, or the data in the date fields of both tables contain a date more than a month ago?

Those. in the example, delete the third row from the first table, because there are no links on it in the second table, and the second row, because the date is overdue.

  • write a query (select) that selects the data you want to delete. ... and change the select ... from expression to delete from . - Yura Ivanov

2 answers 2

deletion of orphaned records

DELETE FROM table1 AS t1 WHERE t1.id NOT IN (SELECT t2.id_s FROM table2 AS t2)

Ps. Never use such removal on real projects.

as far as I know, if you use the LEFT JOIN when deleting, the data will be deleted from both tables

sampling by time interval

SELECT FROM table1 AS t1, table2 AS t2 WHERE t1.date < (NOW() - INTERVAL 30 DAY) AND t2.date < (NOW() - INTERVAL 30 DAY)

  • And why can not this be used? - oleg_ismaylov
  • 1. Such requests will be very resource-intensive when working with large databases. 2. This means that the database is designed poorly and you need to run a script to clean up the "orphaned" records with a certain time interval. - Alexey Alybin
  • Hmm ... And how then to design the base is good?) The events in the first table are stored, and in the second date when these events occur. (well, this is if simplified). If the event has passed, does not prevent it from being deleted? In general, I was going to start the deletion after adding a new record. It will not work out so often, on the other hand, it will not have time to accumulate too much. - oleg_ismaylov
  • then so, with such a deletion of orphaned records there will be no DELETE FROM table1 AS t1 LEFT JOIN table2 AS t2 WHERE t1.id = t2.id AND t2.date < (NOW() - INTERVAL 30 DAY)

as far as I know, if you use the LEFT JOIN when deleting, the data will be deleted from both tables

You can individually delete:

 DELETE `t1` FROM `table1` `t1` LEFT JOIN `table2` `t2` WHERE ISNULL(`t2`.`id`) OR ( `t1`.`date` < NOW() - INTERVAL 1 MONTH AND `t1`.`date` < NOW() - INTERVAL 1 MONTH );

Our English-speaking comrades:

link1

link2

reference3