CODEWITHSUNDEEP
mysqlstored-procedures
fabinho
fabinho

Reputation: 31

Mysql Procedure for division of records

I have a table with 2 columns (ID,IdResponsible) I need to create a procedure that for each record in that table, the column IdResponsible is filled with the counter 1 to 4.

If de counter > 4 then counter = 1 again

How can I do that?

Upvotes: 1

Views: 89

Answers (1)

user8765867
user8765867

Reputation:

You can generate a ranking row number and reset it each 4 rows. Like this:

SELECT t.id, 
   CASE WHEN @rownum >= 4 THEN @rownum := 1
        ELSE @rownum := @rownum + 1 END AS IdResponsible
FROM table1 t, (SELECT @rownum := 0) r;

demo

This will give you something like:

| id | IdResponsible |
|----|---------------|
|  1 |             1 |
|  2 |             2 |
|  3 |             3 |
|  4 |             4 |
|  5 |             1 |
|  6 |             2 |
|  7 |             3 |
|  8 |             4 |
|  9 |             1 |

Upvotes: 2

Related Questions