Update table column with select that uses row_number()

Question

User1395831461 posted

I have a table...

CREATE TABLE [dbo].[APAYsource] (
    [APAY_SRC_CD]    NVARCHAR (100) NULL,
    [TENDER_TYPE_CD] NVARCHAR (100) NULL,
    [EXT_SOURCE_ID]  NVARCHAR (100) NULL,
    [APAY_SRC_NAME]  NVARCHAR (100) NULL,
    [DESCR]          NVARCHAR (100) NULL,
    [BRANCHCOUNTER]  INT            NULL
);

...in which I want to  update the value of column BRANCHCOUNTER with the value from branchenumerator for the corresponding row in the select...

select *, row_number() over (PARTITION by descr order by descr) as branchenumerator from [APAYsource]

I tried to use the row_number() in an update but couldn't get it to work.  I also tried

update [APAYsource for banks] set BRANCHCOUNTER = (select row_number() over (PARTITION by descr order by descr) as branchcounter  from [APAYsource] as T1 where T1.EXT_SOURCE_ID = EXT_SOURCE_ID)

but doesn't work (syntax).

Could someone kindly show me how to do this?  Thanks tonnes for any help, Roscoe

Answer 1

User77042963 posted

Merge  [APAYsource] tgt 
Using (
select *, row_number() over (PARTITION by descr order by descr) as branchenumerator 
from [APAYsource] ) src on src.EXT_SOURCE_ID = tgt.EXT_SOURCE_ID
When matched then 
UPDATE
set BRANCHCOUNTER =branchenumerator;

Answer 2

User1395831461 posted

Thanks tonnes limno