Make new column which is incremented by it's order

Question

I need to make new column for my table Products -> called Order (new column). And using rails migration I need to add new column and instantly set it's order number, but it's need to be done by product_id.

What I mean I need something like:

product_id | order

1 ------------> 1

1 ------------> 2

1 ------------> 3

2 ------------> 1

2 ------------> 2

Is there a way of doing it?

EDIT : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''order' = t1.'order'' at line 15:

update product_submissions t
      join (
       select
       id,
       product_id,
       'order' from  (
          select id,
          product_id,
          @rn:= if(@prev = product_id,@rn:=@rn+1,1) as 'order',
          @prev:=product_id
          from product_submissions,
          (select @rn:=0,@prev:=0)r
          order by product_id,id
         )x
      )t1 on t1.id=t.id set t.'order' = t1.'order'

You need to add some information about your schema, eg Order, Product, and their relationship, and when the above data should be set, etc. — Max Williams, Apr 02 '15 at 12:56
possible duplicate of [Updating column so that it contains the row position](http://stackoverflow.com/questions/4632696/updating-column-so-that-it-contains-the-row-position) — Salman A, Apr 02 '15 at 13:39
Besides, the query you posted does not use correct quotes. Use backticks to enclose column names. — Salman A, Apr 02 '15 at 13:41

score 0 · Accepted Answer · answered Apr 02 '15 at 13:10

Consider the following

mysql> create table test (id int ,product_id int);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into test values (1,1),(2,1),(3,1),(4,2),(5,2);

Now lets create the order

select 
product_id,
`order` from ( 
  select 
  product_id, 
  @rn:= if(@prev = product_id,@rn:=@rn+1,1) as `order`,
  @prev:=product_id from test,(select @rn:=0,@prev:=0)r
  order by product_id,id 
)x ;

This will give you something as

+------------+-------+
| product_id | order |
+------------+-------+
|          1 |     1 |
|          1 |     2 |
|          1 |     3 |
|          2 |     1 |
|          2 |     2 |
+------------+-------+

Now lets use in update command, but before that lets add the column (in your case its already there)

mysql> alter table test add column `order` int ;
Query OK, 5 rows affected (0.29 sec)

mysql> select * from test ;
+------+------------+-------+
| id   | product_id | order |
+------+------------+-------+
|    1 |          1 |  NULL |
|    2 |          1 |  NULL |
|    3 |          1 |  NULL |
|    4 |          2 |  NULL |
|    5 |          2 |  NULL |
+------+------------+-------+
5 rows in set (0.00 sec)

Finally the update command

update test t 
join (
 select 
 id,
 product_id,
 `order` from  ( 
    select id,
    product_id, 
    @rn:= if(@prev = product_id,@rn:=@rn+1,1) as `order`,
    @prev:=product_id 
    from test,(select @rn:=0,@prev:=0)r
    order by product_id,id 
   )x
)t1 on t1.id=t.id set t.`order` = t1.`order`

mysql> select * from test ;
+------+------------+-------+
| id   | product_id | order |
+------+------------+-------+
|    1 |          1 |     1 |
|    2 |          1 |     2 |
|    3 |          1 |     3 |
|    4 |          2 |     1 |
|    5 |          2 |     2 |
+------+------------+-------+
5 rows in set (0.00 sec)

well it gives me errors :( error msg in edited question – Silverfall05 Apr 02 '15 at 13:35 — Silverfall05, Apr 02 '15 at 13:35
Its not single quote `'order'` its backtics ` ` – Abhik Chakraborty Apr 02 '15 at 13:42 — Abhik Chakraborty, Apr 02 '15 at 13:42

Make new column which is incremented by it's order

1 Answers1