Syed Ibrahim Syed Ibrahim - 24 days ago 9
MySQL Question

Copy table details to same table with updated values - MySql

Below is my table details.

Id(A.inc)| Title | StartDate
1 | Program | 2016-11-11T22:05:00
2 | Songs | 2016-11-11T22:10:00
3 | Speech | 2016-11-11T22:15:00
4 | News | 2016-11-11T22:20:00
5 | Debate | 2016-11-11T22:25:00


I need to copy all the above details into the same table with updated values of next day in
StartDate
column and
ID
should be unique auto incremented value.

Here is the details what am expecting is,

Id(A.inc)| Title | StartDate
6 | Program | 2016-11-12T22:05:00
7 | Songs | 2016-11-12T22:10:00
8 | Speech | 2016-11-12T22:15:00
9 | News | 2016-11-12T22:20:00
10 | Debate | 2016-11-12T22:25:00


Is it possible with mysql? Thanks in advance :)

Answer

I'm assuming your Id column is AUTO_INCREMENT-ed and you just want to copy data, not replace. For that, you can try this:

INSERT INTO your_table (Id, Title, StartDate)
SELECT Id, Title, DATE_FORMAT(DATE_ADD(StartDate, INTERVAL 1 DAY), '%Y-%m-%dT%k:%i:%s')
FROM your_table
WHERE Id BETWEEN 1 and 5;

Or simply omit the Id column, because AUTO_INCREMENT-ed column will automatically increment its value.

INSERT INTO your_table (Title, StartDate)
SELECT Title, DATE_FORMAT(DATE_ADD(StartDate, INTERVAL 1 DAY), '%Y-%m-%dT%k:%i:%s')
FROM your_table
WHERE Id BETWEEN 1 and 5;

More about date and time formatting: MySQL Date and Time Functions

Comments