MySQL, delete from multiple tables where id match and timestamp is older than current_time

Question

Well, i have 4 tables, lets call them table1 table2 table3 and table4. In table1 i have a colum called "time" thats beeing updated with CURRENT_TIME on update´s.

So i currently got this procedure to delete old stuff from the database:

DELETE FROM `table1` WHERE `time` < (CURRENT_TIME() - INTERVAL 5 MINUTE);

This should delete all updates older than 5minutes in table1, now in table1, table2, table3 and table4 i got ID´s that matches eachother think of it as employee numbers, so how do i match them up from table1 so i can delete the employees from table2, table3 and table4 at the same time?

Also, its not completly 100% that the id exists in all 4 tables ofcourse, if this is important i dont know.

sidenote: Anyone knows WHEN procedures accually run? :P Thank you for your response! :)

EDIT: sorry, its mysql!

You have tagged both 'mysql' and 'sql-server' ... which flavour of SQL are you using? I'm assuming mysql? — TeamTam, Oct 24 '14 at 00:19
Why don't you make them as foreign key and have on delete cascade? — radar, Oct 24 '14 at 00:21

TeamTam · Accepted Answer · 2014-10-25T05:34:38.793

1

Assuming you're in MySQL, psuedo-code below. Note that I would suggest you wrap it all in a transaction.

START TRANSACTION
BEGIN

  SET @deleteSince = CURRENT_TIME() - INTERVAL 5 MINUTE;

  DELETE FROM table4 WHERE table1ID IN (SELECT FROM table1 WHERE time < @deleteSince
  DELETE FROM table3 WHERE table1ID IN (SELECT FROM table1 WHERE time < @deleteSince
  DELETE FROM table2 WHERE table1ID IN (SELECT FROM table1 WHERE time < @deleteSince
  DELETE FROM table1 WHERE time < @deleteSince

COMMIT

I suggested using a transaction to handle the situation if data changes concurrently from another source between one of the 4 DELETE statements, which would lead to a state of inconsistency in your data. This link will explain this idea more.

Alternatively, you can use the ON DELETE CASCADE feature of MySQL. This link is a tutorial of how to implement this. However, you will most likely need to change your foreign keys and table definitions in your database schema to do this.

If I correctly understand your question as to WHEN procedures run, you will either need to use the MySQL Event Scheduler or set up a Cron job.

edited Oct 25 '14 at 05:34

answered Oct 24 '14 at 00:28

TeamTam

1,598
11
15

It seems like this is doing what i want it to do, im sorry tough im totaly new at sql. could you explain transaction and psuedo-code? Do i paste this in a procedure or what? :) Thank you for your repsone! – Mordin Oct 24 '14 at 18:06
Hi Herman, I will edit my response and provide more explanation. I am new to answering questions on SO! :-) – TeamTam Oct 25 '14 at 05:13
re: 'psuedo-code' ... no you cannot just paste this in and execute it unless I guessed your database schema. Since you did not provide real table/column names, I could not provide the exact query you need. – TeamTam Oct 25 '14 at 05:17
beautiful answere! This really helped me, i cant upvote yet because im new =) will do as soon as i can! – Mordin Oct 31 '14 at 11:47

score 0 · Answer 2 · answered Oct 24 '14 at 02:12

You really have to consider the greater implication of such things. At scale, deleting large transactions cause large roll backs, large replication delays etc. If the number of rows per sub table are unknown then that replication delay can get quite large fast. Rolling back such a transaction is going to cause all kinds of havok. You want to include some limits and loop over those limits until the number of rows affected is 0. In the example given by TeamTam, you want to make sure you have mysql 5.6 or your subqueries will crash and burn the server.

MySQL, delete from multiple tables where id match and timestamp is older than current_time

2 Answers2