SQL Server 2012 – Edit Number Field for All Rows Starting from Specific Integer

sql serversql-server-2012

I have a field "TransactionID", which is datatype number. This field starts from 0, and increases by +1 for each new row. In some cases, there is a need to make this field start from a specific number. Is there a way to go in and update each rows, to start from number xxx, and increase by 1?

Set number xxx
Start from lowest transactionID
Set first rows transactionID to number xxx
Next rows transactionID xxx+1
Next rows transactionID to xxx+2

It's probably like 12 rows that needs to be updated.

This is the whole createtable scripted:

    USE [RetailChannelDatabase]
GO

/****** Object:  Table [ax].[RETAILTRANSACTIONTABLE]    Script Date: 06.12.2016 12:39:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [ax].[RETAILTRANSACTIONTABLE](
    [AMOUNTTOACCOUNT] [numeric](32, 16) NOT NULL,
    [BATCHID] [bigint] NOT NULL,
    [BATCHTERMINALID] [nvarchar](10) NOT NULL,
    [BUSINESSDATE] [date] NOT NULL,
    [CHANNEL] [bigint] NOT NULL,
    [CHANNELREFERENCEID] [nvarchar](50) NOT NULL,
    [COMMENT] [nvarchar](60) NOT NULL,
    [COSTAMOUNT] [numeric](32, 16) NOT NULL,
    [COUNTER] [int] NOT NULL,
    [CREATEDOFFLINE] [int] NOT NULL,
    [CREATEDONPOSTERMINAL] [nvarchar](10) NOT NULL,
    [CURRENCY] [nvarchar](3) NOT NULL,
    [CUSTACCOUNT] [nvarchar](20) NOT NULL,
    [CUSTDISCAMOUNT] [numeric](32, 16) NOT NULL,
    [CUSTPURCHASEORDER] [nvarchar](20) NOT NULL,
    [DESCRIPTION] [nvarchar](150) NOT NULL,
    [DISCAMOUNT] [numeric](32, 16) NOT NULL,
    [DLVMODE] [nvarchar](10) NOT NULL,
    [ENTRYSTATUS] [int] NOT NULL,
    [EXCHRATE] [numeric](32, 16) NOT NULL,
    [FISCALDOCUMENTID] [nvarchar](80) NOT NULL,
    [FISCALSERIALID] [nvarchar](80) NOT NULL,
    [GROSSAMOUNT] [numeric](32, 16) NOT NULL,
    [INCLUDEDINSTATISTICS] [int] NOT NULL,
    [INCOMEEXPENSEAMOUNT] [numeric](32, 16) NOT NULL,
    [INFOCODEDISCGROUP] [nvarchar](10) NOT NULL,
    [INVENTLOCATIONID] [nvarchar](10) NOT NULL,
    [INVENTSITEID] [nvarchar](10) NOT NULL,
    [INVOICECOMMENT] [nvarchar](60) NOT NULL,
    [ITEMSPOSTED] [int] NOT NULL,
    [LOGISTICSPOSTALADDRESS] [bigint] NOT NULL,
    [LOYALTYCARDID] [nvarchar](30) NOT NULL,
    [LOYALTYDISCAMOUNT_RU] [numeric](32, 16) NOT NULL,
    [NETAMOUNT] [numeric](32, 16) NOT NULL,
    [NUMBEROFINVOICES] [int] NOT NULL,
    [NUMBEROFITEMLINES] [numeric](32, 16) NOT NULL,
    [NUMBEROFITEMS] [numeric](32, 16) NOT NULL,
    [NUMBEROFPAYMENTLINES] [int] NOT NULL,
    [OPENDRAWER] [int] NOT NULL,
    [PAYMENTAMOUNT] [numeric](32, 16) NOT NULL,
    [POSTASSHIPMENT] [int] NOT NULL,
    [RECEIPTDATEREQUESTED] [date] NOT NULL,
    [RECEIPTEMAIL] [nvarchar](80) NOT NULL,
    [RECEIPTID] [nvarchar](18) NOT NULL,
    [REFUNDRECEIPTID] [nvarchar](18) NOT NULL,
    [REPLICATED] [int] NOT NULL,
    [REPLICATIONCOUNTERFROMORIGIN] [int] IDENTITY(1,1) NOT NULL,
    [RETRIEVEDFROMRECEIPTID] [nvarchar](18) NOT NULL,
    [ROUNDEDAMOUNT] [numeric](32, 16) NOT NULL,
    [SALEISRETURNSALE] [int] NOT NULL,
    [SALESINVOICEAMOUNT] [numeric](32, 16) NOT NULL,
    [SALESORDERAMOUNT] [numeric](32, 16) NOT NULL,
    [SALESORDERID] [nvarchar](20) NOT NULL,
    [SALESPAYMENTDIFFERENCE] [numeric](32, 16) NOT NULL,
    [SHIFT] [nvarchar](10) NOT NULL,
    [SHIFTDATE] [date] NOT NULL,
    [SHIPPINGDATEREQUESTED] [date] NOT NULL,
    [SKIPAGGREGATION] [int] NOT NULL,
    [STAFF] [nvarchar](25) NOT NULL,
    [STATEMENTCODE] [nvarchar](25) NOT NULL,
    [STORE] [nvarchar](10) NOT NULL,
    [TERMINAL] [nvarchar](10) NOT NULL,
    [TIMEWHENTOTALPRESSED] [int] NOT NULL,
    [TIMEWHENTRANSCLOSED] [int] NOT NULL,
    [TOACCOUNT] [int] NOT NULL,
    [TOTALDISCAMOUNT] [numeric](32, 16) NOT NULL,
    [TOTALMANUALDISCOUNTAMOUNT] [numeric](32, 16) NOT NULL,
    [TOTALMANUALDISCOUNTPERCENTAGE] [numeric](32, 16) NOT NULL,
    [TRANSACTIONID] [nvarchar](44) NOT NULL,
    [TRANSCODE] [int] NOT NULL,
    [TRANSDATE] [date] NOT NULL,
    [TRANSTABLEID] [int] NOT NULL,
    [TRANSTIME] [int] NOT NULL,
    [TYPE] [int] NOT NULL,
    [WRONGSHIFT] [int] NOT NULL,
    [CREATEDDATETIME] [datetime] NOT NULL,
    [MODIFIEDDATETIME] [datetime] NOT NULL,
    [DATAAREAID] [nvarchar](4) NOT NULL,
    [ROWVERSION] [timestamp] NOT NULL,
 CONSTRAINT [I_-1558077251_-1679712867] PRIMARY KEY CLUSTERED 
(
    [STORE] ASC,
    [TERMINAL] ASC,
    [TRANSACTIONID] ASC,
    [CHANNEL] ASC,
    [DATAAREAID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [UI_RETAILTRANSACTIONTABLE_REPLICATIONCOUNTERFROMORIGIN] UNIQUE NONCLUSTERED 
(
    [REPLICATIONCOUNTERFROMORIGIN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_AMOUNTTOACCOUNT]  DEFAULT ((0)) FOR [AMOUNTTOACCOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_BATCHID]  DEFAULT ((0)) FOR [BATCHID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_BATCHTERMINALID]  DEFAULT ('') FOR [BATCHTERMINALID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_BUSINESSDATE]  DEFAULT ('1900-01-01') FOR [BUSINESSDATE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CHANNEL]  DEFAULT ((0)) FOR [CHANNEL]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CHANNELREFERENCEID]  DEFAULT ('') FOR [CHANNELREFERENCEID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_COMMENT]  DEFAULT ('') FOR [COMMENT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_COSTAMOUNT]  DEFAULT ((0)) FOR [COSTAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_COUNTER]  DEFAULT ((0)) FOR [COUNTER]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CREATEDOFFLINE]  DEFAULT ((0)) FOR [CREATEDOFFLINE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CREATEDONPOSTERMINAL]  DEFAULT ('') FOR [CREATEDONPOSTERMINAL]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CURRENCY]  DEFAULT ('') FOR [CURRENCY]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CUSTACCOUNT]  DEFAULT ('') FOR [CUSTACCOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CUSTDISCAMOUNT]  DEFAULT ((0)) FOR [CUSTDISCAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CUSTPURCHASEORDER]  DEFAULT ('') FOR [CUSTPURCHASEORDER]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_DESCRIPTION]  DEFAULT ('') FOR [DESCRIPTION]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_DISCAMOUNT]  DEFAULT ((0)) FOR [DISCAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_DLVMODE]  DEFAULT ('') FOR [DLVMODE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_ENTRYSTATUS]  DEFAULT ((0)) FOR [ENTRYSTATUS]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_EXCHRATE]  DEFAULT ((0)) FOR [EXCHRATE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_FISCALDOCUMENTID]  DEFAULT ('') FOR [FISCALDOCUMENTID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_FISCALSERIALID]  DEFAULT ('') FOR [FISCALSERIALID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_GROSSAMOUNT]  DEFAULT ((0)) FOR [GROSSAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_INCLUDEDINSTATISTICS]  DEFAULT ((0)) FOR [INCLUDEDINSTATISTICS]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_INCOMEEXPENSEAMOUNT]  DEFAULT ((0)) FOR [INCOMEEXPENSEAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_INFOCODEDISCGROUP]  DEFAULT ('') FOR [INFOCODEDISCGROUP]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_INVENTLOCATIONID]  DEFAULT ('') FOR [INVENTLOCATIONID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_INVENTSITEID]  DEFAULT ('') FOR [INVENTSITEID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_INVOICECOMMENT]  DEFAULT ('') FOR [INVOICECOMMENT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_ITEMSPOSTED]  DEFAULT ((0)) FOR [ITEMSPOSTED]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_LOGISTICSPOSTALADDRESS]  DEFAULT ((0)) FOR [LOGISTICSPOSTALADDRESS]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_LOYALTYCARDID]  DEFAULT ('') FOR [LOYALTYCARDID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_LOYALTYDISCAMOUNT_RU]  DEFAULT ((0)) FOR [LOYALTYDISCAMOUNT_RU]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_NETAMOUNT]  DEFAULT ((0)) FOR [NETAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_NUMBEROFINVOICES]  DEFAULT ((0)) FOR [NUMBEROFINVOICES]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_NUMBEROFITEMLINES]  DEFAULT ((0)) FOR [NUMBEROFITEMLINES]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_NUMBEROFITEMS]  DEFAULT ((0)) FOR [NUMBEROFITEMS]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_NUMBEROFPAYMENTLINES]  DEFAULT ((0)) FOR [NUMBEROFPAYMENTLINES]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_OPENDRAWER]  DEFAULT ((0)) FOR [OPENDRAWER]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_PAYMENTAMOUNT]  DEFAULT ((0)) FOR [PAYMENTAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_POSTASSHIPMENT]  DEFAULT ((0)) FOR [POSTASSHIPMENT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_RECEIPTDATEREQUESTED]  DEFAULT ('1900-01-01') FOR [RECEIPTDATEREQUESTED]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_RECEIPTEMAIL]  DEFAULT ('') FOR [RECEIPTEMAIL]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_RECEIPTID]  DEFAULT ('') FOR [RECEIPTID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_REFUNDRECEIPTID]  DEFAULT ('') FOR [REFUNDRECEIPTID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_REPLICATED]  DEFAULT ((0)) FOR [REPLICATED]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_RETRIEVEDFROMRECEIPTID]  DEFAULT ('') FOR [RETRIEVEDFROMRECEIPTID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_ROUNDEDAMOUNT]  DEFAULT ((0)) FOR [ROUNDEDAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SALEISRETURNSALE]  DEFAULT ((0)) FOR [SALEISRETURNSALE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SALESINVOICEAMOUNT]  DEFAULT ((0)) FOR [SALESINVOICEAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SALESORDERAMOUNT]  DEFAULT ((0)) FOR [SALESORDERAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SALESORDERID]  DEFAULT ('') FOR [SALESORDERID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SALESPAYMENTDIFFERENCE]  DEFAULT ((0)) FOR [SALESPAYMENTDIFFERENCE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SHIFT]  DEFAULT ('') FOR [SHIFT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SHIFTDATE]  DEFAULT ('1900-01-01') FOR [SHIFTDATE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SHIPPINGDATEREQUESTED]  DEFAULT ('1900-01-01') FOR [SHIPPINGDATEREQUESTED]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_SKIPAGGREGATION]  DEFAULT ((0)) FOR [SKIPAGGREGATION]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_STAFF]  DEFAULT ('') FOR [STAFF]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_STATEMENTCODE]  DEFAULT ('') FOR [STATEMENTCODE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_STORE]  DEFAULT ('') FOR [STORE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TERMINAL]  DEFAULT ('') FOR [TERMINAL]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TIMEWHENTOTALPRESSED]  DEFAULT ((0)) FOR [TIMEWHENTOTALPRESSED]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TIMEWHENTRANSCLOSED]  DEFAULT ((0)) FOR [TIMEWHENTRANSCLOSED]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TOACCOUNT]  DEFAULT ((0)) FOR [TOACCOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TOTALDISCAMOUNT]  DEFAULT ((0)) FOR [TOTALDISCAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TOTALMANUALDISCOUNTAMOUNT]  DEFAULT ((0)) FOR [TOTALMANUALDISCOUNTAMOUNT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TOTALMANUALDISCOUNTPERCENTAGE]  DEFAULT ((0)) FOR [TOTALMANUALDISCOUNTPERCENTAGE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TRANSACTIONID]  DEFAULT ('') FOR [TRANSACTIONID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TRANSCODE]  DEFAULT ((0)) FOR [TRANSCODE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TRANSDATE]  DEFAULT ('1900-01-01') FOR [TRANSDATE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TRANSTABLEID]  DEFAULT ((0)) FOR [TRANSTABLEID]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TRANSTIME]  DEFAULT ((0)) FOR [TRANSTIME]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_TYPE]  DEFAULT ((0)) FOR [TYPE]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_WRONGSHIFT]  DEFAULT ((0)) FOR [WRONGSHIFT]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_CREATEDDATETIME]  DEFAULT (getutcdate()) FOR [CREATEDDATETIME]
GO

ALTER TABLE [ax].[RETAILTRANSACTIONTABLE] ADD  CONSTRAINT [DF_RETAILTRANSACTIONTABLE_MODIFIEDDATETIME]  DEFAULT (getutcdate()) FOR [MODIFIEDDATETIME]
GO

It's running on SQL Server.

Clarifications (see the chat room for more discussion):

  • It needs to update all rows which are "type" = 2.

  • We basically only need to update the "TransactionID" pk column. Here is an example: 'Store1-Terminal2-3'. The 'Store1-Terminal2' will be the same for all rows, so it's basically only increasing the number at the end.

  • The pattern of all TransactionID values is: 'StoreXX-TerminalYY-ZZZ' where ZZZ is (only digits) an integer and there are exactly two dashes: - separating the three parts.

Best Answer

Under the assumptions:

  • It needs to update all rows which are "type" = 2.

  • We basically only need to update the "TransactionID" pk column. Here is an example: 'Store1-Terminal2-3'. The 'Store1-Terminal2' will be the same for all rows, so it's basically only increasing the number at the end.

  • The pattern of all TransactionID values is: 'StoreXX-TerminalYY-ZZZ' where ZZZ is (only digits) an integer and there are exactly two dashes: - separating the three parts.

We could first manipulate the TransactionID strings (find the last part, convert it integer, add the fixed start_value, etc)):

DECLARE @startvalue INT ;
SET @startvalue = 500 ;

SELECT TransactionID,
       num = CAST(last_part AS INT),
       rn  = @startvalue - 1 + ROW_NUMBER() OVER 
                                 (ORDER BY CAST(last_part AS INT)),
       NewTransactionID 
         = b.first_part 
           + CAST(@startvalue - 1 + ROW_NUMBER() OVER 
                                      (ORDER BY CAST(last_part AS INT))
                  AS NVARCHAR(10))
FROM RETAILTRANSACTIONTABLE AS t
  CROSS APPLY
    ( SELECT 1 + LEN(TransactionID) - CHARINDEX('-', REVERSE(TransactionID))
    ) AS a (first_part_length)
  CROSS APPLY
    ( SELECT SUBSTRING(TransactionID, 1,
                                       a.first_part_length),
             SUBSTRING(TransactionID, 1 + a.first_part_length,  
                                       LEN(TransactionID))
    ) AS b (first_part, last_part)
WHERE t.Type = 2 ;

Once we are satisfied with the results, we can then convert the above to an UPDATE statement:

DECLARE @startvalue INT ;
SET @startvalue = 500 ;

WITH upd AS
    (
    SELECT TransactionID,
           NewTransactionID 
              = b.first_part 
                + CAST(@startvalue -1 + ROW_NUMBER() OVER 
                                          (ORDER BY CAST(last_part AS INT)) 
                       AS NVARCHAR(10))
    FROM RETAILTRANSACTIONTABLE AS t
      CROSS APPLY
        ( SELECT 1 + LEN(TransactionID) - CHARINDEX('-',REVERSE(TransactionID))
        ) AS a (first_part_length)
      CROSS APPLY
        ( SELECT SUBSTRING(TransactionID, 1,
                                          a.first_part_length),
                 SUBSTRING(TransactionID, 1 + a.first_part_length,  
                                          LEN(TransactionID))
        ) AS b (first_part, last_part)
    WHERE t.Type = 2
    )  
UPDATE upd
SET TransactionID = NewTransactionID ;
Related Question