Make sequence autoincrement order number by variable in Mysql

Question

In my example I have this table :

| name | number   |
-------------------
| abc  |          |
| bca  |          |
| sad  |          |
| tyu  |          |
| hjh  |          |
| lpk  |          |
| ass  |          |
| drc  |          |
| dfg  |          |

then i get some variable filled with number like :

$order = 3, then i want to make query to update the table above to look like this :

| name |  number  |
--------------------
| abc  |     1    |
| bca  |     2    |
| sad  |     3    |
| tyu  |     1    |
| hjh  |     2    |
| lpk  |     3    |
| ass  |     1    |
| drc  |     2    |
| dfg  |     3    |

How do I do that in mysql query??

Thanks in advance guys

Ok and what you tried so far?

René Höhle
– René Höhle

2017-11-01 08:33:58 +00:00
Commented Nov 1, 2017 at 8:33 — René Höhle
– René Höhle, Commented Nov 1, 2017 at 8:33
Use modulus logic

Ab Bennett
– Ab Bennett

2017-11-01 08:39:29 +00:00
Commented Nov 1, 2017 at 8:39 — Ab Bennett
– Ab Bennett, Commented Nov 1, 2017 at 8:39

Michael · Accepted Answer · 2017-11-01 08:58:28Z

2

SET @order=3;
UPDATE Table1 SET number2=MOD(number-1,@order)+1;

answered Nov 1, 2017 at 8:58

Michael

5,3452 gold badges16 silver badges37 bronze badges

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

2 Comments

Strawberry Over a year ago

...which begs the question; why store this at all?

www.cahyonegoro.com Over a year ago

works like a charm, but what if the number filed is not relevant now. so all i need is 1 number field

Collectives™ on Stack Overflow

Make sequence autoincrement order number by variable in Mysql

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