CODEWITHSUNDEEP
mysql
J.L. Yokata
J.L. Yokata

Reputation: 115

mysql, update foreach category

it is a really simple question. it is only 1 table! i have a table of books which each book have a category and a number.

SO I WANNA TRANSFORM THIS

+--------------------------+
| book_id | category | num |
+--------------------------+
|    1    |    a     |  7  |
|    2    |    a     |  5  |
|    3    |    a     |  3  |
|    4    |    b     |  9  |
|    5    |    b     |  8  |
|    6    |    b     |  1  |
+--------------------------+

INTO THIS,

+--------------------------+
| book_id | category | num |
+--------------------------+
|    3    |    a     |  3  |
|    2    |    a     |  5  |
|    1    |    a     |  7  |
|    6    |    b     |  1  |
|    5    |    b     |  8  |
|    4    |    b     |  9  |
+--------------------------+

AND THEN THIS!

+--------------------------+
| book_id | category | num |
+--------------------------+
|    3    |    a     |  1  |
|    2    |    a     |  2  |
|    1    |    a     |  3  |
|    6    |    b     |  1  |
|    5    |    b     |  2  |
|    4    |    b     |  3  |
+--------------------------+

BUT HOW?!?!?!?!

script to create table...

drop table if exists books;

CREATE TABLE books(
id int AUTO_INCREMENT,
category varchar(30),
num int,
PRIMARY KEY (id)
);

insert into books (category,num)
values
('a',7),
('a',5),
('a',3),
('b',9),
('b',8),
('b',1);

Upvotes: 2

Views: 67

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can use user variables to generate the sequence numbers within each category in the order of increasing id.

If you just want to query the table, use:

select
    b.id,
    b.category,
    @rn := if(@category = category, @rn + 1, if (@category := category, 1, 1)) num
from books b, (select @category := null, @rn := 0) t2
order by b.category, b.id

If you want to update your table, use:

update books b1
join (
    select
        b.id,
        @rn := if(@category = category, @rn + 1, if (@category := category, 1, 1)) num
    from books b, (select @category := null, @rn := 0) t2
    order by b.category, b.id
) b2 on b1.id = b2.id
set b1.num = b2.num;

Demo

EDIT:

As per the edited question, you can use order by b.category, b.num instead.

Upvotes: 1

Related Questions