Issue with Final Output of Update statement using Window Function

  • Hi All,

    I am updating a column in the table using ranking function where below is the expected result:


    Id  Name Sequel
    A    A1    AA11
    A    A1    AA12
    A    A1    AA13
    B    B1    BB11
    B    B1    BB12
    C    C1    CC11
    C    C2    CC21 

    But output is like this:


    Id  Name Sequel
    A    A1    AA11
    A    A1    AA11
    A    A1    AA11
    B    B1    BB11
    B    B1    BB11
    C    C1    CC11
    C    C2    CC21 

    I have attached the complete script for your reference from Table creation to updating. Can somebody please take a look and let me know if I'm missing something here.
    Please let me know if any addition information is required at my end.
    Any help will be greatly appreciated.

    Regards

  • FFS! Just post the contents of the text file into the message window!
    --Create New Table
        drop table if exists Test_Tbl
        CREATE TABLE Test_Tbl (Id varchar(1), Name varchar(2), sequel varchar(4))

    --Insert some Test data
        insert into Test_Tbl (Id, Name) values ('A','A1')
        insert into Test_Tbl (Id, Name) values ('A','A1')
        insert into Test_Tbl (Id, Name) values ('A','A1')
        insert into Test_Tbl (Id, Name) values ('B','B1')
        insert into Test_Tbl (Id, Name) values ('B','B1')
        insert into Test_Tbl (Id, Name) values ('C','C1')
        insert into Test_Tbl (Id, Name) values ('C','C2')

    --Expected Output
        select Id, Name,
        Id + Name + cast(row_number() over(partition by Id,Name order by Id, Name) as varchar)
        as sequel_Inner
        from Test_Tbl (nolock)

    --Update Script
        update dbo.Test_Tbl
        set sequel = tbl.sequel_Inner
        from
        (select Id, Name,
        Id + Name + cast(row_number() over(partition by Id,Name order by Id, Name) as varchar)
        as sequel_Inner
        from Test_Tbl (nolock) ) as tbl where dbo.Test_Tbl.Id = tbl.Id and dbo.Test_Tbl.Name = tbl.Name

    select * from Test_Tbl (nolock)


    Why the NOLOCK hints? That's not a magic "go faster" button... that's an "allow dirty reads" button.

  • This should do the trick
    WITH cteData AS (
    SELECT
      Id
      , Name
      , sequel
      ,    Id + Name + CAST(ROW_NUMBER() OVER(PARTITION BY Id, Name ORDER BY Id, Name) AS VARCHAR) AS sequel_Inner
        FROM Test_Tbl --WITH (NOLOCK)
    )
    UPDATE cteData
    SET sequel = sequel_Inner;

    You don't need the NOLOCK hints

  • The problem is that you are doing an implicit CROSS JOIN.  Here is the explicit version of what you are doing.

    update tbl
    set sequel = tbl.sequel_Inner
    from Test_Tbl
    CROSS JOIN
    (
        select Id, Name,
        Id + Name + cast(row_number() over(partition by Id,Name order by Id, Name) as varchar)
        as sequel_Inner
        from Test_Tbl
    ) as tbl
    where Test_Tbl.Id = tbl.Id and Test_Tbl.Name = tbl.Name

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply