SQL sequence updating column in reverse order

Question

I'm trying to update a column cohort_number in a table with sequential values from 1 to x (in this case 42) using a seq as below.

CREATE SEQUENCE seq start with 1 increment by 1 no maxvalue no cycle

UPDATE t1
SET cohort_number = next value for seq, instance_number = x.instanceNumber 
FROM 
( 
    SELECT id, 
    1 instanceNumber 
    FROM t1
) x 

DROP SEQUENCE seq

However on update rather than the first row cohort_number = 1, second row = 2 and so on, the first row cohort_column = 42, second row cohort_column = 2. I.e it appears that the update is being processed from the last entry in the select result rather than the first. Is there anyway around this?

I've also tried with ROW_NUMBER as below, but every value for the cohort_column is set to 1, which I believe is due to id having no duplicate values (but I'm guessing here)

UPDATE t1
SET cohort_number = x.cohort_number, instance_number = x.instance_number 
FROM 
( 
    SELECT id, 
    ROW_NUMBER() over (order by id) as cohort_number,
    1 instance_number 
    FROM t1
) x

Does that update statement executes correctly? It's trying to update T1 but it's in a subquery, should be out of scope. — EzLo
– EzLo, Commented Oct 30, 2019 at 12:30
Yeah, its probably where I've obfuscated the table name, variables etc incorrectly, before posting the question — Dave0504
– Dave0504, Commented Oct 30, 2019 at 12:38
Your second query is invalid since the alias t1 exists inside the derived table x and is not visible outside of x. Now would be a good time to post a simple script that demonstrates your problem that everyone can use as a common starting point. But perhaps this is no reason to actually store a value at all - sounds much more like a XY problem — SMor
– SMor, Commented Oct 30, 2019 at 12:58

David Browne - Microsoft · Accepted Answer · 2019-10-30 13:05:05Z

2

The strange and proprietary UPDATE ... FROM syntax makes my head hurt.

This:

UPDATE t1
SET cohort_number = x.cohort_number, instance_number = x.instance_number 
FROM 
( 
    SELECT id, 
    ROW_NUMBER() over (order by id) as cohort_number,
    1 instance_number 
    FROM t1
) x

Doesn't work, because you're updating T1, without correlating it with x, and your're not updating x. Either would work.

I find it much easier to write a SELECT query that returns all the rows that will be updated, along with the new values for columns to be updated. Then update that:

with x as
(
    SELECT id, 
    cohort_number
    instance_number,
    ROW_NUMBER() over (order by id) as new_cohort_number,
    1 new_instance_number 
    FROM t1
)
update x set cohort_number = new_cohort_number, instance_number = new_instance_number

answered Oct 30, 2019 at 13:05

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

1 Comment

Dave0504 Over a year ago

Cheers, was banging my head against a brick wall here.

Gordon Linoff · Accepted Answer · 2019-10-30 15:00:00Z

2

Why would you use a sequence for this? Just use row_number():

UPDATE toupdate
    SET cohort_number = seqnum,
        instance_number = new_instanceNumber 
    FROM (SELECT t1.*, ROW_NUMBER() OVER (ORDER BY id DESC) as seqnum
                 1 as new_instanceNumber 
          FROM t1
         ) toupdate;

edited Oct 30, 2019 at 15:00

answered Oct 30, 2019 at 12:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Dave0504 Over a year ago

I've tried with row_number but I seem to be doing something wrong... I'll update the question to show what I've tried.

Gordon Linoff Over a year ago

@Dave0504 . . . I don't get why this would work, except for the typo (now fixed) around t.*.

Dave0504 Over a year ago

It didn't, the accepted answer shows where I was going wrong.Cheers.

Collectives™ on Stack Overflow

SQL sequence updating column in reverse order

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related