none
update a table with max value + row number (based on 2 column partitions) RRS feed

  • Question

  • Hello Experts,

    I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

    job_cd permit_nbr reference_nbr
    ABC1 990 100002
    ABC1 990 100002
    ABC1 991 100003
    ABC1 992 100004
    ABC1 993 100005
    ABC2 880 100006
    ABC2 881 100007
    ABC2 881 100007
    ABC2 882 100008
    ABC2 882 100008

    Please help me in building the query.

    Thanks in advance.

    Naveen


    Naveen J V

    Tuesday, September 15, 2015 9:04 AM

Answers

  • Hi Naveen,

                 Hope this Helps.

    CREATE TABLE [dbo].[RefNumberUpdate]
         (
    	   [JobCode] 					[NVARCHAR](50) 		NOT NULL
    	 , [PermitNumber] 				[INT] 			NOT NULL
    	 , [ReferenceNumber] 				[INT]
    )
    GO
    
    INSERT
      INTO [dbo].[RefNumberUpdate] VALUES
     ('ABC1',990,100002)
    ,('ABC1',990,100002)
    ,('ABC1',991,100003)
    ,('ABC1',992,100004)
    ,('ABC1',993,100005)
    ,('ABC2',880,100006)
    ,('ABC2',881,100007)
    ,('ABC2',881,100007)
    ,('ABC2',882,100008)
    ,('ABC2',882,100008)
    ,('ABC3',999,NULL)
    ,('ABC3',999,NULL)
    ,('ABC4',998,NULL)
    ,('ABC5',997,NULL)
    ,('ABC5',996,NULL)
    GO
    
    SELECT [JobCode], [PermitNumber], [ReferenceNumber]
      FROM [dbo].[RefNumberUpdate]
    GO
    
      JobCode	PermitNumber	ReferenceNumber
      ABC1	990	100002
      ABC1	990	100002
      ABC1	991	100003
      ABC1	992	100004
      ABC1	993	100005
      ABC2	880	100006
      ABC2	881	100007
      ABC2	881	100007
      ABC2	882	100008
      ABC2	882	100008
      ABC3	999	NULL
      ABC3	999	NULL
      ABC4	998	NULL
      ABC5	997	NULL
      ABC5	996	NULL
      
    
    SELECT [JobCode], [PermitNumber], [ReferenceNumber]
         , ( Min_Ref_Number + Increment - 1) AS [NewReferenceNumber]
      FROM (
    SELECT [JobCode], [PermitNumber], [ReferenceNumber]
         , MIN([ReferenceNumber]) OVER() AS Min_Ref_Number
         , DENSE_RANK() OVER (ORDER BY [JobCode], [PermitNumber]) AS Increment
      FROM [dbo].[RefNumberUpdate]
         ) Input
     WHERE Input.[ReferenceNumber]  IS NULL
     GO
    
     JobCode	PermitNumber	ReferenceNumber	NewReferenceNumber
    ABC3	999	NULL	100009
    ABC3	999	NULL	100009
    ABC4	998	NULL	100010
    ABC5	996	NULL	100011
    ABC5	997	NULL	100012


    --Raj

    • Proposed as answer by Raja B Tuesday, September 15, 2015 8:46 PM
    • Unproposed as answer by Raja B Wednesday, September 16, 2015 8:50 AM
    • Proposed as answer by Raja B Friday, September 18, 2015 10:14 PM
    • Marked as answer by Naveen JV Tuesday, September 22, 2015 10:34 AM
    Tuesday, September 15, 2015 8:38 PM

All replies

  • WITH CTE AS (
       SELECT reference_nbr, row_number() OVER (ORDER BY (SELECT 1)) AS rowno
       FROM   tbl
       WHERE  reference_nbr IS NULL
    )
    UPDATE CTE
    SET    references_nbr = rowno + (SELECT MAX(reference_nbr FROM tbl)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, September 15, 2015 10:25 AM
  • create table test (job_cd varchar(10),	permit_nbr int,	reference_nbr int)
    Insert into test (job_cd,permit_nbr,reference_nbr) values
    ('ABC1',990,100002)
    ,('ABC1',990,100002)
    ,('ABC1',991,100003)
    ,('ABC1',992,100004)
    ,('ABC1',993,100005)
    ,('ABC2',880,100006)
    ,('ABC2',881,100007)
    ,('ABC2',881,100007)
    ,('ABC2',882,100008)
    ,('ABC2',882,100008)
    
    ,('ABC3',333,null)
    
    ,('ABC4',444,null)
    ,('ABC4',444,null)
     
     update test
     set reference_nbr=null;
    
     
    Declare @i int=10001
    ;with mycte as (select *,  
    row_number() OVER (PARTITION BY job_cd, permit_nbr Order by job_cd, permit_nbr asc)  rn
    from test
    )
    
    
    ,mycte1 as (
    Select *, @i+Sum(Case when rn>1 then 0 else 1 end )  Over(Order by job_cd, permit_nbr asc) newVal
    from mycte)
    
    Update mycte1
    Set reference_nbr= newVal
    
    Select * from Test
    
    drop table test

    Tuesday, September 15, 2015 2:49 PM
    Moderator
  • Hi Naveen,

                 Hope this Helps.

    CREATE TABLE [dbo].[RefNumberUpdate]
         (
    	   [JobCode] 					[NVARCHAR](50) 		NOT NULL
    	 , [PermitNumber] 				[INT] 			NOT NULL
    	 , [ReferenceNumber] 				[INT]
    )
    GO
    
    INSERT
      INTO [dbo].[RefNumberUpdate] VALUES
     ('ABC1',990,100002)
    ,('ABC1',990,100002)
    ,('ABC1',991,100003)
    ,('ABC1',992,100004)
    ,('ABC1',993,100005)
    ,('ABC2',880,100006)
    ,('ABC2',881,100007)
    ,('ABC2',881,100007)
    ,('ABC2',882,100008)
    ,('ABC2',882,100008)
    ,('ABC3',999,NULL)
    ,('ABC3',999,NULL)
    ,('ABC4',998,NULL)
    ,('ABC5',997,NULL)
    ,('ABC5',996,NULL)
    GO
    
    SELECT [JobCode], [PermitNumber], [ReferenceNumber]
      FROM [dbo].[RefNumberUpdate]
    GO
    
      JobCode	PermitNumber	ReferenceNumber
      ABC1	990	100002
      ABC1	990	100002
      ABC1	991	100003
      ABC1	992	100004
      ABC1	993	100005
      ABC2	880	100006
      ABC2	881	100007
      ABC2	881	100007
      ABC2	882	100008
      ABC2	882	100008
      ABC3	999	NULL
      ABC3	999	NULL
      ABC4	998	NULL
      ABC5	997	NULL
      ABC5	996	NULL
      
    
    SELECT [JobCode], [PermitNumber], [ReferenceNumber]
         , ( Min_Ref_Number + Increment - 1) AS [NewReferenceNumber]
      FROM (
    SELECT [JobCode], [PermitNumber], [ReferenceNumber]
         , MIN([ReferenceNumber]) OVER() AS Min_Ref_Number
         , DENSE_RANK() OVER (ORDER BY [JobCode], [PermitNumber]) AS Increment
      FROM [dbo].[RefNumberUpdate]
         ) Input
     WHERE Input.[ReferenceNumber]  IS NULL
     GO
    
     JobCode	PermitNumber	ReferenceNumber	NewReferenceNumber
    ABC3	999	NULL	100009
    ABC3	999	NULL	100009
    ABC4	998	NULL	100010
    ABC5	996	NULL	100011
    ABC5	997	NULL	100012


    --Raj

    • Proposed as answer by Raja B Tuesday, September 15, 2015 8:46 PM
    • Unproposed as answer by Raja B Wednesday, September 16, 2015 8:50 AM
    • Proposed as answer by Raja B Friday, September 18, 2015 10:14 PM
    • Marked as answer by Naveen JV Tuesday, September 22, 2015 10:34 AM
    Tuesday, September 15, 2015 8:38 PM