CODEWITHSUNDEEP
sqloracle-database
Milos Bogetic
Milos Bogetic

Reputation: 23

Updating a number column with a new string of numbers starting at particular number

So, I have a table ITEMS. It has columns NAME, DATE, and NUMBER. Currently, Number column looks something like this:

NUMBER
300000  
300001  
300002

It has about 750 entries. I'd like to update the Number column with sequential numbers starting at 400045, increasing by 1.

Additionally, there are a few items there already with the number 400000 (i.e. between 400000 and 400045) so I wouldn't want to update those.

I've tried stuff such as:

DECLARE @id INT   
SET @id = 400000   
UPDATE **ITEMS**  
SET @id = **NUMBER** = @id + 1

Upvotes: 2

Views: 248

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I guess below query is generic enough to work for you -

UPDATE ITEMS
SET NUMBER = (SELECT rn 
              FROM (SELECT NUMBER, 
                           ROW_NUMBER() OVER (ORDER BY NUMBER) + 400045 rn
                    FROM ITEMS
                    WHERE NUMBER NOT BETWEEN 400000 AND 400045) X
              WHERE X.NUMBER = ITEMS.NUMBER)

Though you should not keep column name as number as this might be confusing for you.

Upvotes: 2

Related Questions