Return to Answer

updated after question clarification

Source Link

edited Dec 6, 2016 at 15:59

2.4k
1
12
22

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

/if exists (select * settingfrom uptempdb.sys.objects testwhere dataname */like '#retailtransactiontable%') begin; drop table #retailtransactiontable; end;
create table retailtransactiontable#retailtransactiontable (
    [store] [nvarchar](10) not null
  , [terminal] [nvarchar](10) not null
  , [type] [int] not null
  , [transactionid] [nvarchar](44) not null);
    
insert into retailtransactiontable#retailtransactiontable (store, terminal, [type], transactionid)
select topvalues
('1','2',1,'Store1-Terminal2-T1'), 10('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')
 number 
select * from [master]..spt_values v#retailtransactiontable;

declare @FirstTransactionIdToUpdate whereint number%4=0= 5;
declare @NewTransactionIdStart   and number>22
  groupint by= number;2;

with cte as (
select * from retailtransactiontable;select 
        store
/* start of update statement */ , terminal
declare @FirstTransactionIdToUpdate bigint = 32;  , type
declare @NumberOfRowsToUpdate     , transactionid
 bigint     , transint = 5;convert(int,(right(transactionid,charindex('-',reverse(transactionid))-1)))
declare @NewTransactionIdStart     , bigintnewtransactionid = 64;
'Store'+convert(nvarchar(10),Store)
with cte as                       + '-Terminal'+convert(nvarchar(22),Terminal)
   select top                     +'-'+convert(@NumberOfRowsToUpdatenvarchar(3) 
                            ,((@NewTransactionIdStart+(row_number() over (order by transactionid))-1)))
      --, newid = (@NewTransactionIdStart+(row_number() over (order by transactionid))-1)
      from retailtransactiontable#retailtransactiontable
      where transactionidstore='1'
 >= @FirstTransactionIdToUpdate      and terminal='2'
      order by and [type]=2
        and convert(int,(right(transactionid,charindex('-',reverse(transactionid))-1))) >= @FirstTransactionIdToUpdate
    )
    --select * from cte
    update cte set transactionid = newid;newtransactionid;

select * from retailtransactiontable;#retailtransactiontable;

results in: beforebefore:

store      terminal   type        transactionid
24
28
32
36---------- ---------- ----------- --------------------------------------------
401          2          1           Store1-Terminal2-T1
441          2          2           Store1-Terminal2-1
481          2          2           Store1-Terminal2-13
521          2          2           Store1-Terminal2-5
561          2          2           Store1-Terminal2-7
601          2          2           Store1-Terminal2-9

store      terminal   type        transactionid
24
28
64
65---------- ---------- ----------- --------------------------------------------
661          2          1           Store1-Terminal2-T1
671          2          2           Store1-Terminal2-1
681          2          2           Store1-Terminal2-2
521          2          2           Store1-Terminal2-3
561          2          2           Store1-Terminal2-4
601          2          2           Store1-Terminal2-5

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

/* setting up test data */
create table retailtransactiontable (
    [transactionid] [nvarchar](44) not null);
    
insert into retailtransactiontable (transactionid)
select top 10 
     number 
  from [master]..spt_values v
  where number%4=0 
    and number>22
  group by number;
  
select * from retailtransactiontable;

/* start of update statement */
declare @FirstTransactionIdToUpdate bigint = 32;
declare @NumberOfRowsToUpdate       bigint = 5;
declare @NewTransactionIdStart      bigint = 64;

with cte as (
   select top (@NumberOfRowsToUpdate) 
        transactionid
      , newid = (@NewTransactionIdStart+(row_number() over (order by transactionid))-1)
      from retailtransactiontable
      where transactionid >= @FirstTransactionIdToUpdate
      order by transactionid
    )
    
    update cte set transactionid = newid;

select * from retailtransactiontable;

results in: before:

transactionid
24
28
32
36
40
44
48
52
56
60

transactionid
24
28
64
65
66
67
68
52
56
60

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

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

Source Link

created Dec 6, 2016 at 13:35

SqlZim

2.4k
1
12
22

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

/* setting up test data */
create table retailtransactiontable (
    [transactionid] [nvarchar](44) not null);
    
insert into retailtransactiontable (transactionid)
select top 10 
    number 
  from [master]..spt_values v
  where number%4=0 
    and number>22
  group by number;
  
select * from retailtransactiontable;

/* start of update statement */
declare @FirstTransactionIdToUpdate bigint = 32;
declare @NumberOfRowsToUpdate       bigint = 5;
declare @NewTransactionIdStart      bigint = 64;

with cte as (
   select top (@NumberOfRowsToUpdate) 
        transactionid
      , newid = (@NewTransactionIdStart+(row_number() over (order by transactionid))-1)
      from retailtransactiontable
      where transactionid >= @FirstTransactionIdToUpdate
      order by transactionid
    )
    
    update cte set transactionid = newid;

select * from retailtransactiontable;

results in: before:

transactionid
24
28
32
36
40
44
48
52
56
60

after:

transactionid
24
28
64
65
66
67
68
52
56
60