DEV Community

For loops in MySQL...kind of

Gavin Sykes on December 06, 2023

Hi everyone, sorry it's been a while! I had a bit of a breakthrough today, finally achieving (or at least emulating the behaviour of) a for loop i...

Read full post

Tim Bray • Dec 6 '23

It would be way more sensible to have a table called 'review_periods' with the time period.

Then join this table to multiply out the number of reviews. So insert into review alerts select alert_date = review_due_date - interval review_periods.period month, ..., from review_periods join .....

remember that join means mulitply :)

( and i'm never a fan of triggers because they can be changed without any version control)

Julien Dephix • Dec 7 '23

Nice. INSERT INTO ... SELECT can be very interesting to work with indeed.

Note: I'm pretty sure you do not need to SELECT NEW.review_alert_id, you can just use the value directly.

BEGIN
  INSERT INTO `review_alert_acknowledgements` (`review_alert_id`,`user_id`) SELECT DISTINCT
    NEW.review_alert_id,
    users.user_id
    FROM `users`
    WHERE users.role = 'admin';
END