Row_number function

I'm trying to update a column called Rank in X table where erank would be the rank of a column called annual sales and it is computed using row_number () over (order by annual sales desc) and as this i开发者_如何转开发s a function, hence it cannot be used to update as it should only be a part of select clause so we have written something like this :

UPDATE X 
   SET rank = SELECT acc_id,
                     annual_call,
                     ROW_NUMBER() OVER (ORDER BY annual sales DESC)
                FROM x 
            GROUP BY acc_id,annual_call

But this is throwing an error

As general rule, when you ask a question and you mention an error, say what error you get. It help us help you, you don't have to make it a mystery novel...

Here is how you do it:

with cte as (
select [rank], row_number() over (
   partition by acc_id, annual_call
   order by [annual sales] desc) as [row_number]
from x)
update cte
set [rank] = [row_number];

Of course, persisting such rank is usually doomed since it will become incorrect after first update, but that is a different topic.

Here while

set rank = select acc_id,annual_call,row_number () 
over (order by annual sales desc)
from x

must select only 1 column, you must implement some other logic for this. as assigning the values must return only a single column.