Issue with Final Output of Update statement using Window Function

• T.Ashish

  SSCarpal Tunnel

  Points: 4248

  More actions

  August 3, 2017 at 11:43 am

  #402391

  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

• pietlinden

  SSC Guru

  Points: 63506

  More actions

  August 3, 2017 at 12:05 pm

  #1954026

  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.

• DesNorton

  SSC-Insane

  Points: 24396

  More actions

  August 3, 2017 at 12:09 pm

  #1954027

  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

  ---

  How to post data/code on a forum to get the best help.

• drew.allen

  SSC Guru

  Points: 76995

  More actions

  August 3, 2017 at 1:21 pm

  #1954036

  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