CODEWITHSUNDEEP
phpmysql
Syed Ibrahim
Syed Ibrahim

Reputation: 583

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 :)

Upvotes: 0

Views: 32

Answers (1)

Hermanto
Hermanto

Reputation: 552

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

Upvotes: 2

Related Questions