locked
Update table column with select that uses row_number() RRS feed

  • 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

    Wednesday, November 2, 2016 6:57 PM

Answers

  • 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;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2016 7:04 PM

All replies

  • 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;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2016 7:04 PM
  • User1395831461 posted

    Thanks tonnes limno

    Wednesday, November 2, 2016 7:36 PM