Edit number field for all rows. Start from integer xxx, then +1

Question

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.

Let us continue this discussion in chat.
– miniHessel
Commented Dec 6, 2016 at 11:50 — miniHessel, Commented Dec 6, 2016 at 11:50

Community · Accepted Answer · 2020-06-15 09:05:18Z

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 ;

SqlZim · Accepted Answer · 2016-12-06 15:59:16Z

If I'm reading the question right, you could use a combination of a few variables, a cte, and the row_number() function to write this type statement. You can add more conditions to the cte to get to the exact data set you are looking for, and drop the TOP variable if it isn't helpful.

e.g. rextester: http://rextester.com/OIBEDZ93140 updated: http://rextester.com/TGZR4250

if exists (select * from tempdb.sys.objects where name like '#retailtransactiontable%') begin; drop table #retailtransactiontable; end;
create table #retailtransactiontable (
    [store] [nvarchar](10) not null
  , [terminal] [nvarchar](10) not null
  , [type] [int] not null
  , [transactionid] [nvarchar](44) not null);

insert into #retailtransactiontable (store, terminal, [type], transactionid) values
('1','2',1,'Store1-Terminal2-T1'), ('1','2',2,'Store1-Terminal2-1'), ('1','2',2,'Store1-Terminal2-13'), ('1','2',2,'Store1-Terminal2-5'), ('1','2',2,'Store1-Terminal2-7'), ('1','2',2,'Store1-Terminal2-9')

select * from #retailtransactiontable;

declare @FirstTransactionIdToUpdate int = 5;
declare @NewTransactionIdStart      int = 2;

with cte as (
   select 
        store
      , terminal
      , type
      , transactionid
      , transint = convert(int,(right(transactionid,charindex('-',reverse(transactionid))-1)))
      , newtransactionid = 'Store'+convert(nvarchar(10),Store)
                         + '-Terminal'+convert(nvarchar(22),Terminal)
                         +'-'+convert(nvarchar(3)
                            ,((@NewTransactionIdStart+(row_number() over (order by transactionid))-1)))
      --, newid = (@NewTransactionIdStart+(row_number() over (order by transactionid))-1)
      from #retailtransactiontable
      where store='1'
        and terminal='2'
        and [type]=2
        and convert(int,(right(transactionid,charindex('-',reverse(transactionid))-1))) >= @FirstTransactionIdToUpdate
    )
    --select * from cte
    update cte set transactionid = newtransactionid;

select * from #retailtransactiontable;

before:

store      terminal   type        transactionid
---------- ---------- ----------- --------------------------------------------
1          2          1           Store1-Terminal2-T1
1          2          2           Store1-Terminal2-1
1          2          2           Store1-Terminal2-13
1          2          2           Store1-Terminal2-5
1          2          2           Store1-Terminal2-7
1          2          2           Store1-Terminal2-9

after:

store      terminal   type        transactionid
---------- ---------- ----------- --------------------------------------------
1          2          1           Store1-Terminal2-T1
1          2          2           Store1-Terminal2-1
1          2          2           Store1-Terminal2-2
1          2          2           Store1-Terminal2-3
1          2          2           Store1-Terminal2-4
1          2          2           Store1-Terminal2-5

Stack Exchange Network

Edit number field for all rows. Start from integer xxx, then +1

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2012
or ask your own question.

Hot Network Questions

Edit number field for all rows. Start from integer xxx, then +1

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2012 or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2012
or ask your own question.