SQL UPDATE row Number -

i have table serviceclusters column identity(1590 values). have table serviceclustersnew columns id, text , comment. in table, have values text , comment, id 1. here example table:

[1, dummy1, hello1;

1, dummy2, hello2;

1, dummy3, hello3;

etc.]

whai want values in column id continuing index of table serviceclusters plus current row number: in our case, 1591, 1592 , 1593.

i tried solve problem this: first updated column id maximum value, tryed add row number, doesnt work:

-- update id maximum value 1590 update serviceclustersnew  set id = (select max(id) serviceclusters); -- command returns correct values 1591, 1592 , 1593 select id+row_number() on (order text_id) rownumber  serviceclustersnew -- i'm not able update table command update serviceclustersnew  set id = (select id+row_number() on (order text_id) rownumber serviceclustersnew) 

by sending last command, error "syntax error: ordered analytical functions not allowed in subqueries.". have suggestions, how solve problem? know volatile table or adding column, there way without creating new table / altering current table?

you have rewrite using update from, syntax bit bulky:

update serviceclustersnew  (    select text_id,       (select max(id) serviceclusters) +       row_number() on (order text_id) newid     serviceclustersnew  ) src set id = newid serviceclustersnew.text_id = src.text_id