0

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 <= @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.

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.

0
6

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.

4
  • If works good, only it is 2015-08-06T15:00:00 and not 2131-08-06T15:00:00. Oct 19 '15 at 14:08
  • @Hein you mean you want to update the 2131-08-06 datetimes with 2015-08-06 datetimes? Oct 19 '15 at 14:23
  • @Hein I was going by your screen shot, which has 2131-08-06. Your question is all over the place, in the comment you say 2015-08-06T15:00:00 but your code has 2015-10-19 0:0:0 as the starting point. Please be specific and accurate. Oct 19 '15 at 14:31
  • @AaronBertrand: oops, it a mistake by myself. but i have done this DATEADD(YEAR, -116, @creatie) and it is good. in the table there is only fake data so I have change it. Oct 19 '15 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.