x

Update a Table Column Increment by 1

 I have a table A (ID, AppNumber) that has duplicates. I want to update the AppNumber field increment by 1, and so on. 
 Can anyone help?
 
 Table A:
 ID AppNumber
 45     160
 45    160
 45    160
 45    160
 62    165
 62    165
 62    165
 73    159
 73    159
 
 Result should like this:
 ID AppNumber
 45     160
 45    161
 45    162
 45    163
 62    165
 62    166
 62    167
 73    159
 73    160
more ▼

asked Mar 17 at 11:00 PM in Default

avatar image

Sunshine15
241 1 2 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hey, You can try the following. I just scripted this and haven't tested it. So no guarantees. Its long and winding and am sure some folks will come up with a better solution. But as a side note I think you should fix the issue of duplicates in your table instead of manipulating data.

 IF EXISTS ( SELECT  *
             FROM    sys.Objects
             WHERE   [Name] = 'TableA' ) 
     BEGIN 
         DROP TABLE TableA
     END
  
 GO
 
 CREATE TABLE [dbo].[TableA]
     (
       [ID] [int] NULL ,
       [AppNumber] [int] NULL
     )
 ON  [PRIMARY]
 
 
 GO
 
 INSERT  [dbo].[TableA]
         ( [ID], [AppNumber] )
 VALUES  ( 45, 160 ),
         ( 45, 160 ),
         ( 45, 160 ),
         ( 45, 160 ),
         ( 62, 165 ),
         ( 62, 165 ),
         ( 62, 165 ),
         ( 73, 159 ),
         ( 73, 159 )
 
 GO
 
 ;
 WITH    CTE_AppNumbers
           AS ( SELECT   ID ,
                         AppNumber ,
                         ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY AppNumber ) AS Num
                FROM     TableA
              ),
         CTE_OrderAppNumbers
           AS ( SELECT   A.ID ,
                         A.AppNumber ,
                         B.Num ,
                         LEAD(A.Num, 1) OVER ( PARTITION BY A.AppNumber ORDER BY A.Num DESC ) AS AppNumberOrder
                FROM     CTE_AppNumbers A
                         INNER JOIN CTE_AppNumbers B ON A.Num = B.Num
                                                        AND B.AppNumber = A.AppNumber
              ),
         CTE_UpdatedAppNumbers
           AS ( SELECT   ID ,
                         AppNumber ,
                         NewAppNumber = ( AppNumber + COALESCE(AppNumberOrder,
                                                               0) ) ,
                         Num
                FROM     CTE_OrderAppNumbers
              )
     UPDATE  A
     SET     A.AppNumber = N.NewAppNumber
     FROM    CTE_AppNumbers A
             INNER JOIN CTE_UpdatedAppNumbers N ON A.ID = N.ID
                                                   AND A.Num = N.Num
 
 SELECT  ID ,
         AppNumber
 FROM    TableA
 /*
 ID AppNumber
 45 160
 45 161
 45 162
 45 163
 62 165
 62 166
 62 167
 73 159
 73 160
 
 */
 ROLLBACK TRAN
more ▼

answered Mar 18 at 12:33 PM

avatar image

mdawini
81 1 4

It works. Thank you.

Mar 20 at 03:41 PM Sunshine15

Always welcome

Mar 20 at 04:13 PM mdawini
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1149

asked: Mar 17 at 11:00 PM

Seen: 47 times

Last Updated: Mar 20 at 04:13 PM

Copyright 2017 Redgate Software. Privacy Policy