Abraham Putra Prakasa Abraham Putra Prakasa - 3 months ago 23
MySQL Question

MySQL changing numbers per batch

I have table with column like this:

id (int auto inc primary), title (varchar), batch (int)

which characteristic is having 3 item per batch. But in this case the batch is not in sequence

Here the sample data:


  • 1,a,5

  • 2,b,5

  • 3,c,5

  • 4,d,7

  • 5,e,7

  • 6,f,7

  • 7,g,10

  • 8,h,10



Is there any query to update those batches become like auto increment? (order by batch) So batch 5 become 1, batch 7 become 2, batch 10 become 3 and so on.

TL;DR: I want to update sequence per batch, not per row

Thanks for any help.

Answer

You can do it like this:

UPDATE t
JOIN (
SELECT
t.*,
@newbatch := IF (@rc % 3 = 0, @newbatch + 1, @newbatch) AS newbatch,
@rc := @rc + 1
FROM t
, (SELECT @rc := 0, @newbatch := 0) var_init_subquery
) sq ON t.id = sq.id
SET t.batch = newbatch;

If you have questions about how this works, please read this manual entry about user defined variables first.