I have a table as follows i want to increment the datetime by 1minute for each id so the outcome should look like how can i achieve this in an update statement all at once? since i have like 100 rows Answer You can use an updatable CTE: db<>fiddle

how to update multiple rows in a single column to use a different time

I have a table as follows

ID   | DateTime
1    |2021-06-10 14:25:00.000
11   |2021-06-10 14:25:00.000
15   |2021-06-10 14:25:00.000
18   |2021-06-10 14:25:00.000
111  |2021-06-10 14:25:00.000

i want to increment the datetime by 1minute for each id

so the outcome should look like

  ID      | DateTime
    1    |2021-06-10 14:26:00.000
    11   |2021-06-10 14:27:00.000
    15   |2021-06-10 14:28:00.000
    18   |2021-06-10 14:29:00.000
    111  |2021-06-10 14:30:00.000

how can i achieve this in an update statement all at once? since i have like 100 rows

Answer

You can use an updatable CTE:

WITH CTE AS(
    SELECT DateTime,
           ROW_NUMBER() OVER (ORDER BY ID) AS N
    FROM dbo.YourTable)
UPDATE CTE
SET DateTime = DATEADD(MINUTE, N, DateTime);

db<>fiddle

Advertisement

Answer