mysql: update increment sequence number with same number previous row

Question

i have a table look like this:

table: fruits

------------
num | fruit
------------
1   | apple
1   | banana
2   | orange
2   | apple
2   | guava
3   | grape
3   | strawberry
4   | blueberry
4   | watermelon
4   | honeydew
4   | apple

i want to update the sequence to the number that i choose, example start from 600, i tried something like this:

UPDATE fruits
JOIN (SELECT @rank := 600) r
SET num=@rank:=@rank+1;

it become:

------------
num   | fruit
------------
601   | apple
602   | banana
603   | orange
604   | apple
605   | guava
606   | grape
607   | strawberry
608   | blueberry
609   | watermelon
610   | honeydew
611   | apple

result that i want is:

------------
num   | fruit
------------
621   | apple
621   | banana
622   | orange
622   | apple
622   | guava
623   | grape
623   | strawberry
624   | blueberry
624   | watermelon
624   | honeydew
624   | apple

so that previous num with 1 will become 601 and 2 will become 602. Any idea? thanks

Gordon Linoff · Accepted Answer · 2019-10-04 03:51:53Z

1

How about just using +?

UPDATE fruits
    SET num = num + 600;

(In your example, you seem to want + 620 rather than + 600.)

answered Oct 4, 2019 at 3:51

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

Teddybugs Over a year ago

What if I want to start in some random number like 564, how the sequence going to work?

Gordon Linoff Over a year ago

@Teddybugs . . . You would adjust the 600 to be the appropriate offset.

Collectives™ on Stack Overflow

mysql: update increment sequence number with same number previous row

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related