Teddybugs
Teddybugs

Reputation: 1244

mysql: update increment sequence number with same number previous row

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

Upvotes: 0

Views: 125

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

How about just using +?

UPDATE fruits
    SET num = num + 600;

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

Upvotes: 1

Related Questions