0

I'm a beginner to SQL and would like to write a script that deletes rows from 2 tables of a MySQL database.

I have events and attendees and like to delete all attendee and event data from events that are older than 90 days. So I need to find an array of event_ids of old events and then delete all attendees refering to any event_id in that array.

What's the best way to solve that? Here is my idea:

DELETE * FROM attendees WHERE event_id = ANY (SELECT id FROM events WHERE enddate <= DATE_ADD(CURDATE(), INTERVAL -90 DAY))

DELETE * FROM events WHERE enddate <= DATE_ADD(CURDATE(), INTERVAL -90 DAY)
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Buzzwave
  • 7
  • 2
  • Is the tables are connected with the foreign key constraints ? – Abhik Chakraborty Jun 24 '15 at 09:32
  • attendees have a foreign key event_id that refers to the events table primary keys. – Buzzwave Jun 24 '15 at 09:37
  • Your idea will work, [here](http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) is a better solution. Also you can add a ON DELETE CASCADE foreign key. – Vatev Jun 24 '15 at 09:38
  • you should use foreign key constraints – Amrinder Singh Jun 24 '15 at 09:44
  • I did not create the tables and don't want to interfere other operations, so I don't want to use [foreign key constraints](http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html) with ON DELETE CASCADE. But I see it would be a great way to do this in general. – Buzzwave Jun 24 '15 at 09:59
  • Thanks to everyone. Didn't think I'd be on the right track with my code at all. But in the I just had to add `ANY`. – Buzzwave Jun 24 '15 at 12:53

2 Answers2

1

Delete query should not use * 

DELETE  FROM attendees WHERE event_id = IN  (SELECT id FROM events WHERE enddate <= DATE_ADD(CURDATE(), INTERVAL -90 DAY))


DELETE  FROM events WHERE enddate <= DATE_ADD(CURDATE(), INTERVAL -90 DAY)

(EDIT: Added ANY to deal with multiple lines in subquery)

jameshwart lopez
  • 2,993
  • 6
  • 35
  • 65
0

You should set up a foreign key relationship from attendees to event with on delete cascade.

Then, when you delete an event, the attendees will automatically be deleted.

You can read more about foreign key relationships here.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have not created the databases. But if this relationship has been set on creation and the events will be deleted (only second line of my code), this will mean, that attendees are also deleted automatically? – Buzzwave Jun 24 '15 at 09:48
  • Just checked and that's a very nice way to do that, [find more](http://www.mysqltutorial.org/mysql-on-delete-cascade/) Unfortunatelly that will affect other operations on that database I do not want to influence. – Buzzwave Jun 24 '15 at 09:55
  • @user2014895 . . . I find it hard to imagine that you would want an `attendees` table that has an `event_id` that is not valid. Maintaining the consistency is called relational integrity, and maintaining relational integrity is an important function of databases. – Gordon Linoff Jun 24 '15 at 10:10