Duplicate row 50 times but change the date field recurring interval mysql

Question

0.00/5 (No votes)

See more:

Hi I have a table called work_events ( id, date_time, title). It has 1 row: (1,2024-09-07 11:00, GroupMeeting)

No, i want to copy that row like 51 times. the only thing is that the date on each row moves on 7 days....so basiclly I want the same row recurring every week...as this is actually shown in a calendar. However, it has to be a copy of rows...so each event has its own row...the id needs to increase IS there an a way to do this using sql?

What I have tried:

I have no idea where to start but I want to do this using an sql query...

Posted 6-Sep-24 4:01am

Mark Donners 2022

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2024-09-06T04:19:00

I haven't tried this but, looking at your table, something like this might work:

SQL

INSERT INTO work_events (date_time, title)
WITH RECURSIVE sequence(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM sequence WHERE n < 51
)
SELECT DATE_ADD(we.date_time, INTERVAL (7 * s.n) DAY) as date_time, we.title
FROM work_events we
CROSS JOIN sequence s
WHERE we.id = 1;

Note that this is making the assumption that your id field is auto-incrementing.

We are using a Common Table Expression (CTE)[^] here to do the heavy lifting of the processing, making use of the fact that we have a predictable week numbering to apply here.