Loop over all records in a table and edit a column in SQL Server How can you loop over all the rows in a SQL Server table and edit a column? You know that:


There are a lot of IDs skipped.
The column creatie must be edit whit every time one minute. So the first must be 15:00:00, the second 15:01:00, the third 15:02:00 etc.
In total are there 54 records.
I use SQL Server 2014 Express version


I have used the query below but the maximum ID is 24697... So the loop is very long.

declare @var datetime = '2015-10-19 0:0:0';
declare @counter int = 1;
declare @max int = (select max(id) from topic);

while (@counter &lt;= @max) begin
    update Topic set Creatie = Creatie + @var where id = @counter;
    set @counter += 1;
    set @var += '0-0-0 0:1:0'
end;


Because I do every time @var += '0-0-0 0:1:0', I don't know what the result goes be after 24697 times (if it will works).

Here are the top 25 rows.



PS1: the year 2131 is a mistake my myself. don't look at it and use the year 2015.

PS2: I work with a 24h clock.
No, don't loop. Any time you think in SQL "I need to loop over this set and do something to every row" change your thinking to "I need to do something to every row in this set."

DECLARE @start SMALLDATETIME = '2015-08-06T15:00:00';

;WITH x AS 
(
  SELECT id, creatie, rn = ROW_NUMBER() OVER (ORDER BY id) - 1
  FROM dbo.Topic
  --WHERE creatie = '2131-08-06T15:00:00'
)
SELECT id, creatie, DATEADD(MINUTE, rn, @start) FROM x ORDER BY id;
-- UPDATE x SET creatie = DATEADD(MINUTE, rn, @start);


When you are happy that this does what you expect, comment the SELECT and uncomment the UPDATE.