Update query by order of sequence

Question

I am having an issue trying to use an update query:

select      
    lc.name as locations,
    lc.sequence
from        
    dbo.LocationContainers lc

Currently this query shows this result:

locations       sequence
-------------------------
014-001-010-01  10
014-001-010-02  10
014-001-010-03  10
014-001-010-04  10
014-001-010-05  10
014-001-010-06  10
014-001-010-07  10
014-001-010-08  10
014-001-010-09  10
014-001-010-10  10
014-001-020-01  10
014-001-020-02  10
014-001-020-03  10
014-001-020-04  10
014-001-020-05  10
014-001-020-06  10
014-001-020-07  10
014-001-020-08  10
014-001-020-09  10
014-001-020-10  10
014-001-030-01  10
014-001-030-02  10
014-001-030-03  10
014-001-030-04  10
014-001-030-05  10
014-001-030-06  10
014-001-030-07  10
014-001-030-08  10
014-001-030-09  10
014-001-030-10  10
014-030-010-01  10
014-030-010-02  10
014-030-010-03  10
014-030-010-04  10
014-030-010-05  10
014-030-010-06  10
014-030-010-07  10
014-030-010-08  10
014-030-010-09  10
014-030-010-10  10
014-030-020-01  10
014-030-020-02  10
014-030-020-03  10
014-030-020-04  10
014-030-020-05  10
014-030-020-06  10
014-030-020-07  10
014-030-020-08  10
014-030-020-09  10
014-030-020-10  10
014-030-030-01  10
014-030-030-02  10
014-030-030-03  10
014-030-030-04  10
014-030-030-05  10
014-030-030-06  10
014-030-030-07  10
014-030-030-08  10
014-030-030-09  10
014-030-030-10  10

but what I need to changed is the sequence starts at location 014-001-010-01 sequence 10 and ends at 014-030-030-10 to 610

so basically goes by 10 from 014-001-010-01 = 10 then 014-030-010-01 20 and so on then 014-001-010-02 30 and then 014-030-010-02 40 and so on

locations       sequence
------------------------
014-001-010-01  10
014-001-010-02  30
014-001-010-03  50
014-001-010-04  70
014-001-010-05  90
014-001-010-06  110
014-001-010-07  130
014-001-010-08  150
014-001-010-09  170
014-001-010-10  190
014-001-020-01  210
014-001-020-02  230
014-001-020-03  250
014-001-020-04  270
014-001-020-05  290
014-001-020-06  310
014-001-020-07  330
014-001-020-08  350
014-001-020-09  370
014-001-020-10  390
014-001-030-01  410
014-001-030-02  430
014-001-030-03  450
014-001-030-04  470
014-001-030-05  490
014-001-030-06  510
014-001-030-07  530
014-001-030-08  550
014-001-030-09  570
014-001-030-10  600
014-030-010-01  20
014-030-010-02  40
014-030-010-03  60
014-030-010-04  80
014-030-010-05  100
014-030-010-06  120
014-030-010-07  140
014-030-010-08  160
014-030-010-09  180
014-030-010-10  200
014-030-020-01  220
014-030-020-02  240
014-030-020-03  260
014-030-020-04  280
014-030-020-05  300
014-030-020-06  320
014-030-020-07  340
014-030-020-08  360
014-030-020-09  380
014-030-020-10  400
014-030-030-01  420
014-030-030-02  440
014-030-030-03  460
014-030-030-04  480
014-030-030-05  500
014-030-030-06  520
014-030-030-07  540
014-030-030-08  560
014-030-030-09  580
014-030-030-10  610

Your pattern appears to always add 20 to the previous sequence. Except at 014-001-030-09 where it takes a jump of 30 from 570 to 600. Please clarify the pattern. — Bart McEndree
– Bart McEndree, Commented Jan 24 at 20:44
is actually by 10 each pattern example starts at 014-001-010-01 10 then next 014-030-010-01 20 then 014-001-010-02 30 then 014-030-010-02 40 and so on — icodesomething
– icodesomething, Commented Jan 24 at 20:48
FYI, SQL Server 2008 reached end of life almost 6 years ago. It hasn't had any security updates since 2019, and lacks a wealth of functionality that modern versions have. You urgently need to get onto a supported version. Using such an outdated & insecure version is a significant concern for you, your employer, and your clients. — Thom A
– Thom A ♦, Commented Jan 24 at 21:29
I dont know why it picked sql server 2008 I am using sql server 2016. — icodesomething
– icodesomething, Commented Jan 24 at 21:30
Try select location, row_number()over(order by location)*10 as sequence from LocationContainers — ValNik
– ValNik, Commented Jan 24 at 21:57

ValNik · Accepted Answer · 2025-01-24 23:10:02Z

0

Possible
your l.name have template as
abc-def-ghi-jk
014-001-010-01

Take each part of name and use in proper order in row_number() partition and order clause.

See example

select 
   row_number()over(order by abc,ghi,jk,def)*10 sequenceNew
  ,row_number()over(order by abc,ghi,jk,def)*10 -sequence dif
  ,lo.* 
from(select *
  ,substring(l.name,1,3)  abc
  ,substring(l.name,9,3)  ghi
  ,substring(l.name,13,2) jk
  ,substring(l.name,5,3)  def
from locations l
) lo
order by sequence;

fiddle

edited Jan 24 at 23:10

answered Jan 24 at 23:04

ValNik

7,8581 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update query by order of sequence

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related