Add specific RowNumbers

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 27, 2007 at 8:28 am

  #697159

  --Table

  CREATE TABLE LineNums (INVOICE int, linenum int)

  --Data

  INSERT INTO LineNums (INVOICE) VALUES (12345)

  INSERT INTO LineNums (INVOICE) VALUES (12345)

  INSERT INTO LineNums (INVOICE) VALUES (12346)

  INSERT INTO LineNums (INVOICE) VALUES (12347)

  INSERT INTO LineNums (INVOICE) VALUES (12347)

  INSERT INTO LineNums (INVOICE) VALUES (12347);

  --Update

  WITH CTE AS

    (SELECT INVOICE, linenum, ROW_NUMBER() OVER (PARTITION BY INVOICE ORDER BY INVOICE) AS linenum2

     FROM LineNums)

  UPDATE CTE SET linenum = linenum2

  --Test it

  SELECT * FROM LineNums

  John

• Jason Coleman

  SSCrazy

  Points: 2187

  More actions

  March 27, 2007 at 9:00 am

  #697180

  Cheers John that works a treat...

  got some brushing up to do on my t-sql code!