Sql-server – Loop over all records in a table and edit a column in SQL Server

sql serversql server 2014update

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.

^{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.

Best Answer

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.

Related Solutions

Sql-server – Query to normalize table/combine row text

This should work, I will clean it up later so its more efficient.

DECLARE @Old TABLE ( 
  id         INT, 
  rank       INT, 
  linenumber INT, 
  sometext   VARCHAR(1000)) 
DECLARE @New TABLE ( 
  id           INT, 
  rank         INT, 
  combinedtext VARCHAR(1000)) 


;WITH combinedresults(ctid, id, rank, linenumber, combinedtext) 
     AS (SELECT 0, 
                id, 
                rank, 
                linenumber, 
                CAST (sometext AS VARCHAR(8000)) 
         FROM   @Old o 
         WHERE  NOT EXISTS (SELECT TOP 1 1 
                            FROM   @Old 
                            WHERE  id = o.id 
                                   AND rank = o.rank 
                                   AND linenumber < o.linenumber) 
         UNION ALL 
         SELECT ctid + 1, 
                o.id, 
                o.rank, 
                o.linenumber, 
                ct.combinedtext + o.sometext 
         FROM   @Old o 
                INNER JOIN combinedresults ct 
                  ON ct.id = o.id 
                     AND ct.rank = o.rank 
         WHERE  o.linenumber > ct.linenumber) 

UPDATE n 
SET    combinedtext = ct.combinedtext 
FROM   @New n 
       INNER JOIN (SELECT n.id, 
                          n.rank, 
                          MAX(o.rank) orank 
                   FROM   @new n 
                          INNER JOIN @Old o 
                            ON n.id = o.id 
                               AND o.rank <= n.rank 
                   GROUP  BY n.id, 
                             n.rank) r 
         ON n.id = r.id 
            AND n.rank = r.rank 
       INNER JOIN (SELECT id, 
                          ct.rank, 
                          MAX(ctid) ctid 
                   FROM   combinedresults ct 
                   GROUP  BY ct.id, 
                             ct.rank) r2 
         ON r2.id = r.id 
            AND r2.rank = r.orank 
       INNER JOIN combinedresults ct 
         ON r.id = ct.id 
            AND ct.rank = r.orank 
            AND ct.ctid = r2.ctid 

SELECT * 
FROM   @New

Sql-server – Select all records, join with table A if join exists, table B if not

Here is the first approach I came up with:

DECLARE @ChosenLanguage INT = 48;

SELECT sc.Id, Result = MAX(COALESCE(
   CASE WHEN lst.LanguageId = @ChosenLanguage      THEN st.Text END,
   CASE WHEN lst.LanguageId = sk.DefaultLanguageId THEN st.Text END)
)
FROM dbo.SupportCategories AS sc
INNER JOIN dbo.StringKeys AS sk
  ON sc.StringKeyId = sk.Id
LEFT OUTER JOIN dbo.LanguageStringTranslations AS lst
  ON sk.Id = lst.StringKeyId
  AND lst.LanguageId IN (sk.DefaultLanguageId, @ChosenLanguage)
LEFT OUTER JOIN dbo.StringTranslations AS st
  ON st.Id = lst.StringTranslationId
  --WHERE sc.Id = 1
  GROUP BY sc.Id
  ORDER BY sc.Id;

Basically, get the potential strings that match the chosen language and get all the default strings, then aggregate so you only pick one per Id - prioritize on the chosen language, then take the default as a fallback.

You can probably do similar things with UNION/EXCEPT but I suspect this will almost always lead to multiple scans against the same objects.

Best Answer

Related Solutions

Sql-server – Query to normalize table/combine row text

Sql-server – Select all records, join with table A if join exists, table B if not

Related Question