SQL UPDATE row Number

13,161

Solution 1

You have to rewrite it using UPDATE FROM, the syntax is just a bit bulky:

UPDATE serviceClustersNew
FROM
 (
   SELECT text_id,
      (SELECT MAX(ID) FROM serviceClusters) +
      ROW_NUMBER() OVER (ORDER BY Text_ID) AS newID 
   FROM serviceClustersNew
 ) AS src
SET ID = newID
WHERE serviceClustersNew.Text_ID = src.Text_ID

Solution 2

You are not dealing with a lot of data, so a correlated subquery can serve the same purpose:

UPDATE serviceClustersNew 
    SET ID = (select max(ID) from serviceClustersNew) +
             (select count(*)
              from serviceClustersNew scn2
              where scn2.Text_Id <= serviceClustersNew.TextId
             )

This assumes that the text_id is unique along the rows.

Share:
13,161
el smu
Author by

el smu

Updated on June 04, 2022

Comments

  • el smu
    el smu almost 2 years

    I have a table serviceClusters with a column identity(1590 values). Then I have another table serviceClustersNew with the columns ID, text and comment. In this table, I have some values for text and comment, the ID is always 1. Here an example for the table:

    [1, dummy1, hello1;

    1, dummy2, hello2;

    1, dummy3, hello3;

    etc.]

    WhaI want now for the values in the column ID is the continuing index of the table serviceClusters plus the current Row number: In our case, this would be 1591, 1592 and 1593.

    I tried to solve the problem like this: First I updated the column ID with the maximum value, then I tryed to add the row number, but this doesnt work:

    -- Update ID to the maximum value 1590
    UPDATE serviceClustersNew 
    SET ID = (SELECT MAX(ID) FROM serviceClusters);
    -- This command returns the correct values 1591, 1592 and 1593
    SELECT ID+ROW_NUMBER() OVER (ORDER BY Text_ID) AS RowNumber 
    FROM serviceClustersNew
    -- But I'm not able to update the table with this command
    UPDATE serviceClustersNew 
    SET ID = (SELECT ID+ROW_NUMBER() OVER (ORDER BY Text_ID) AS RowNumber FROM
    serviceClustersNew)
    

    By sending the last command, I get the error "Syntax error: Ordered Analytical Functions are not allowed in subqueries.". Do you have any suggestions, how I could solve the problem? I know I could do it with a volatile table or by adding a column, but is there a way without creating a new table / altering the current table?