I have tried this query but it didn't work:

UPDATE phonebooks SET created_at = DATE_ADD(created_at, INTERVAL 10 second)

My current records are like this :

I want to get these records like this :

CodePudding user response：

Right now you are just adding 10 seconds to each row, but you need something that would increase your interval depending of the row you are updating. Based on your example, you could use the ID as a multiplier.

UPDATE phonebooks SET created_at = DATE_ADD(created_at, INTERVAL 10 * id second)

This won't work exactly as specified if you have gaps in your IDs.

CodePudding user response：

A dynamic solution for an unknown position in the table. will use user defined variables.

The starting point of the time is determined by the UPDATE WHERE clause

CREATE TABLE table1 (
  `ID` INTEGER,
  `DATE` VARCHAR(19)
);

INSERT INTO table1
  (`ID`, `DATE`)
VALUES
  ('1', '2022-03-24 10:30:34'),
  ('2', '2022-03-24 10:30:34'),
  ('3', '2022-03-24 10:30:34'),
  ('4', '2022-03-24 10:30:34'),
  ('5', '2022-03-24 10:30:34'),
  ('6', '2022-03-24 10:30:34'),
  ('7', '2022-03-24 10:30:34');

SET @date = ''

UPDATE table1 SET `DATE` = IF(@date = '', @date := DATE_ADD(`DATE`, INTERVAL 10 second), @date := DATE_ADD(@date, INTERVAL 10 second) )
WHERE `DATE` >= '2022-03-24 10:30:34'

SELECT * FROM table1

ID | DATE               
-: | :------------------
 1 | 2022-03-24 10:30:44
 2 | 2022-03-24 10:30:54
 3 | 2022-03-24 10:31:04
 4 | 2022-03-24 10:31:14
 5 | 2022-03-24 10:31:24
 6 | 2022-03-24 10:31:34
 7 | 2022-03-24 10:31:44

db<>fiddle here