2

I need to create a single MySQL statement for speeding up the process of updating weekly repeating events in a database table I have.

Right now, the user is adding records to the table. Some records are for single date events and some records are events that repeat every 7 days within a defined start date and end date. Both types of records are entered into the same table.

Each event has the start date and an end date of the event date entered in two fields: 'date_start' and 'date_end'. For a single date event the user enters the same date in 'date_start' and 'date_end'. For a repeating event, they are entering the date of the first event occurence in 'date_start' and date of the last date of the weekly occurence in 'date_end'. Repeating events are marked as 'Weekly_Event' as shown below in the sample data.

I need to take this table and make multiple copies of all records marked 'Weekly_Event'. One copy of the original record for each week between the start and end date.

In the example below a record has the start date set as May 3 and end date as June 7, I need a SQL statement that creates 6 new records from that record: May 3, May 10, May 17, May 24, May 31, June 7. There is also another 'Weekly_Event' and a 'Single_Day' event a sample.

MySQL version: 8.0.36
Table Type: InnoDB  
Collation: latin1_swedish_ci


CREATE TABLE `original_shows_24` (
  `id` int NOT NULL AUTO_INCREMENT,
  `status` int NOT NULL DEFAULT '1',
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `name` varchar(200) NOT NULL,
  `details` mediumtext NOT NULL,
  `url` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'https://',
  `slug` varchar(128) NOT NULL,
  `category` varchar(300) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `original_shows_24` (`id`, `status`, `date_start`, `date_end`, `name`, `details`, `url`, `slug`, `category`) VALUES
(1, 1, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event', 'Weekly_Event'),
(2, 1, '2024-05-09', '2024-08-18', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event', 'Weekly_Event'),
(3, 1, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'Details about this event ', 'https://link', 'a-single-day-event', 'Single_Day');

After the operation, the records would mostly look like the below sample. I want the outputted date_start and date_end of the Weekly_Events equal because each of the newly generated events happen within their assigned single day.


CREATE TABLE `updated_shows_24` (
  `id` int NOT NULL AUTO_INCREMENT,
  `status` int NOT NULL DEFAULT '1',
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `name` varchar(200) NOT NULL,
  `details` mediumtext NOT NULL,
  `url` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'https://',
  `slug` varchar(128) NOT NULL,
  `category` varchar(300) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `updated_shows_24` (`id`, `status`, `date_start`, `date_end`, `name`, `details`, `url`, `slug`, `category`) VALUES
(1, 0, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event', 'Weekly_Event'),
(2, 0, '2024-05-09', '2024-08-18', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event', 'Weekly_Event'),
(3, 1, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'Details about this event ', 'https://link', 'a-single-day-event', 'Single_Day'),
(101, 1, '2024-05-03', '2024-05-03', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-03', 'Weekly_Event'),
(102, 1, '2024-05-10', '2024-05-10', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-10', 'Weekly_Event'),
(103, 1, '2024-05-17', '2024-05-17', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-17', 'Weekly_Event'),
(104, 1, '2024-05-24', '2024-05-24', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-24', 'Weekly_Event'),
(105, 1, '2024-05-31', '2024-05-31', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-31', 'Weekly_Event'),
(106, 1, '2024-06-07', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-06-07', 'Weekly_Event'),
(107, 1, '2024-05-09', '2024-05-09', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event-2024-05-09', 'Weekly_Event'),
(108, 1, '2024-05-16', '2024-05-16', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event-2024-05-16', 'Weekly_Event');

The 'id' for the new events should be unique and ideally just be auto generated based on the current next 'id' in 'original_shows_24'. In my example I am assuming that 'id' is in use in 'original_shows_24' up to 100 by other records.

Note the 'status' of the events used to generate the repeating events. It is set to '0' in the records that have been processed ('date_start' not equal to 'date_end' and 'category' = 'Weekly_Event')

Another element of the final output that needs to be considered: the generated slug. The slug field is used to generate the link to the unique sub page for that event so it needs to be updated in this operation as well. Note the format in the sample data, basically I need to append a dash ('-') and 'date_start' on the end of the slug for each of the new records generated. All entries in 'slug' must be unique.

It also will need to be able to generate the correct date as the month changes. For example, the last event in the sample data is June 7. This is 7 days after May 31. If MySQL automatically takes care of this in date operations then that is great.

I have only created 'updated_shows_24' to show the output. If the output can just be put back in 'original_shows_24' (with the appropriate records status changed from '1' to '0') that is best. Also 'updated_shows_24' only shows the first two records of the other repeating event just to illustrate the desired output. Since the end date of that repeating event is 2024-08-18, there would be many more records outputted by the solution than just the two IU have included.

Thanks for any help.

4
  • Provide precise MySQL version. Provide sample data according to Tips for asking a good Structured Query Language (SQL) question. Why SD and ED in desired output are equal?
    – Akina
    Commented Feb 2 at 5:02
  • Thanks for that, Akina -- I have edited and updated the question. In reformatting the question, it exposed a bunch of other considerations that I completely overlooked. I also see how formatting the question this way makes it far easier for people answering to actually see what is going on and what is needed :)
    – hamilton9
    Commented Feb 2 at 14:46
  • Are you using MySQL's EVENTs?
    – Rick James
    Commented Feb 4 at 18:58
  • SELECT '2023-05-31' + INTERVAL 7 DAY; gives 2023-06-07
    – Rick James
    Commented Feb 4 at 19:00

1 Answer 1

2

DEMO

CREATE TABLE `original_shows_24` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `name` varchar(200) NOT NULL,
  `slug` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `original_shows_24` (`id`,`date_start`, `date_end`, `name`, `slug`) VALUES
(1, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'weekly-event'),
(2, '2024-05-09', '2024-06-18', 'Another Weekly Event', 'another-weekly-event'),
(3, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'a-single-day-event');

SELECT * FROM original_shows_24;
id date_start date_end name slug
1 2024-05-03 2024-06-07 Weekly Event Name weekly-event
2 2024-05-09 2024-06-18 Another Weekly Event another-weekly-event
3 2024-05-02 2024-05-02 A regular Single Day Event a-single-day-event
INSERT INTO original_shows_24 (date_start, date_end, name, slug)
WITH RECURSIVE cte AS (
  SELECT date_start, date_end, name, slug
  FROM original_shows_24
  WHERE date_start < date_end
  UNION ALL
  SELECT LEAST(date_end, date_start + INTERVAL 1 WEEK), date_end, name, slug
  FROM cte
  WHERE date_start < date_end
)
SELECT date_start, date_start, name, CONCAT(slug, '-', date_start)
FROM cte;
SELECT * FROM original_shows_24 ORDER BY name, date_start, date_end DESC
id date_start date_end name slug
3 2024-05-02 2024-05-02 A regular Single Day Event a-single-day-event
2 2024-05-09 2024-06-18 Another Weekly Event another-weekly-event
5 2024-05-09 2024-05-09 Another Weekly Event another-weekly-event-2024-05-09
7 2024-05-16 2024-05-16 Another Weekly Event another-weekly-event-2024-05-16
9 2024-05-23 2024-05-23 Another Weekly Event another-weekly-event-2024-05-23
11 2024-05-30 2024-05-30 Another Weekly Event another-weekly-event-2024-05-30
13 2024-06-06 2024-06-06 Another Weekly Event another-weekly-event-2024-06-06
15 2024-06-13 2024-06-13 Another Weekly Event another-weekly-event-2024-06-13
16 2024-06-18 2024-06-18 Another Weekly Event another-weekly-event-2024-06-18
1 2024-05-03 2024-06-07 Weekly Event Name weekly-event
4 2024-05-03 2024-05-03 Weekly Event Name weekly-event-2024-05-03
6 2024-05-10 2024-05-10 Weekly Event Name weekly-event-2024-05-10
8 2024-05-17 2024-05-17 Weekly Event Name weekly-event-2024-05-17
10 2024-05-24 2024-05-24 Weekly Event Name weekly-event-2024-05-24
12 2024-05-31 2024-05-31 Weekly Event Name weekly-event-2024-05-31
14 2024-06-07 2024-06-07 Weekly Event Name weekly-event-2024-06-07

fiddle

The original data has been edited, and the ranges have been slightly shortened. Columns that are unnecessary for demonstration have also been removed.

ORDER BY provides visibility of the result.

category is not used because the event category can be determined by date_start and date_end equiality.

3
  • Thank you, this is a great answer, I appreciate it. For the original repeating event records (id=1, 2 in your example), is there a way to automate setting their status = 0 from status = 1 after the repeating events are created?
    – hamilton9
    Commented Feb 3 at 0:31
  • 1
    @hamilton9 is there a way to automate setting their status = 0 from status = 1 after the repeating events are created? "After" may be wrong solution in concurrent environ. You may expand final SELECT, add complete values for the rows whose status should be changed with UNION ALL, and use INSERT .. ODKU. The query will insert newly created weekly one-day rows and update existing multi-day rows at the same time.
    – Akina
    Commented Feb 3 at 20:16
  • Very good solution, thank you.
    – hamilton9
    Commented Feb 4 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.