Increment an existing value by 10 each new field-CodePudding

I need some help I need to increment the my existing field pick sequence where all a value of 2600, what I need is that field needs to keep increasing example:

picksequence
2600
2610
2620
2630

And the query

with        test
as
(
select      lc.name as location,
            lc.pickSequence,
            lc.pickSequence   10 as testing


from        dbo.LocationContainers lc

where       lc.parentId = 8601

)

select      *
from        test


order by    location

the query gives this results now

CodePudding user response：

You can use ROW_NUMBER, butyou need to define an ORDEr BY so that you get the proper sequence

with        test
as
(
select      lc.name as location,
            lc.pickSequence,

            lc.pickSequence   (ROW_NUMBER() OVER(ORDER BY name) * 10) as testing


from        dbo.LocationContainers lc

where       lc.parentId = 8601

)

select      *
from        test


order by    location

location | pickSequence | testing
:------- | -----------: | ------:
loc-1    |         2600 |    2610
loc-2    |         2600 |    2620
loc-3    |         2600 |    2630
loc-4    |         2600 |    2640

db<>fiddle here