CODEWITHSUNDEEP
mysql
Cynial
Cynial

Reputation: 680

use MySQL to update fields with group

id     item_id    item_order
---    ---------- -----------
1      1          0
2      1          0
3      1          0

4      2          0
5      2          0
6      2          0

7      3          0
8      3          0
9      3          0
10     3          0

How to use MySQL statement to update the order fields to:

id     item_id    item_order
---    ---------- -----------
1      1          1
2      1          2
3      1          3

4      2          1
5      2          2
6      2          3

7      3          1
8      3          2
9      3          3
10     3          4

Upvotes: 3

Views: 274

Answers (3)

Sheepy
Sheepy

Reputation: 17995

Subquery can provide an elegance solution. Assuming your table is called test:

UPDATE test a SET item_order = ( 
  SELECT count(*) FROM ( SELECT id, item_id FROM test ) b
  WHERE a.item_id = b.item_id AND b.id <= a.id
)

How it works? For each row it would query the number of rows with same item_id and lower or equal id. This may be not as scalable as other solutions, I haven't tested, but for smaller tables this is easier to understand and update.

Notice that b is another subquery. This would force MySQL to create an internal temp table so that it won't complain we are reading from the table we are updating.

Upvotes: 5

Nicola Cossu
Nicola Cossu

Reputation: 56357

Select version:

select id,item_id,
@num:= if(@item_id=item_id,@num+1,1) as posit,
@item_id:=item_id as itid
from tab,(select @num:=0,@item_id:=0) as t order by item_id,id

+----+---------+-------+------+
| id | item_id | posit | itid |
+----+---------+-------+------+
|  1 |       1 |     1 |    1 |
|  2 |       1 |     2 |    1 |
|  3 |       1 |     3 |    1 |
|  4 |       2 |     1 |    2 |
|  5 |       2 |     2 |    2 |
|  6 |       2 |     3 |    2 |
|  7 |       3 |     1 |    3 |
|  8 |       3 |     2 |    3 |
|  9 |       3 |     3 |    3 |
| 10 |       3 |     4 |    3 |
+----+---------+-------+------+
10 rows in set (0.33 sec)

Update version:

update tab as t1 
inner join ( 
select id,item_id,
@num:= if(@item_id=item_id,@num+1,1) as posit,
@item_id:=item_id as itid
from tab,(select @num:=0,@item_id:=0) as t order by item_id,id )
as t2 
set t1.item_order = t2.posit
where t1.id = t2.id

+----+---------+------------+
| id | item_id | item_order |
+----+---------+------------+
|  1 |       1 |          1 |
|  2 |       1 |          2 |
|  3 |       1 |          3 |
|  4 |       2 |          1 |
|  5 |       2 |          2 |
|  6 |       2 |          3 |
|  7 |       3 |          1 |
|  8 |       3 |          2 |
|  9 |       3 |          3 |
| 10 |       3 |          4 |
+----+---------+------------+
10 rows in set (0.00 sec)

Upvotes: 1

ajreal
ajreal

Reputation: 47321

do this 

create table tmp
(
  id int(10) unsigned,
  `order` int(10) unsigned auto_increment,
  ... // any other columns
 primary key (id, `order`)
);

insert into tmp select * from old_table; // you can specify order if you want to

after getting the order correctly,
you can then remove the primary key on two columns

NOTE -- avoid reserved words as column name at all cost

IT WORKS

mysql> select * from test;
+------+--------+
| id   | orders |
+------+--------+
|    1 |      0 |
|    1 |      0 |
|    1 |      0 |
|    2 |      0 |
|    2 |      0 |
|    2 |      0 |
|    2 |      0 |
+------+--------+
7 rows in set (0.00 sec)

mysql> insert into tmp select * from test;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+--------+
| id | orders |
+----+--------+
|  1 |      1 |
|  1 |      2 |
|  1 |      3 |
|  2 |      1 |
|  2 |      2 |
|  2 |      3 |
|  2 |      4 |
+----+--------+
7 rows in set (0.00 sec)

mysql> desc tmp;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(10) | NO   | PRI | 0       |                |
| orders | int(10) | NO   | PRI | NULL    | auto_incrent |
+--------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Upvotes: 1

Related Questions