Insert a new column based on existing column values

Question

I have a table table1 in mysql like this:

price        item     count
100          xyz      5
200          xyz      1
300          xyz      4
400          abc      1
500          abc      2

I want to insert a new column 'new_price' that will hold the 'price' for that 'item' with the highest 'count'. So the new table will be

price        item     count     new_price
100          xyz      5         100
200          xyz      1         100
300          xyz      4         100
400          abc      1         500
500          abc      2         500

What is the most efficient way of doing this? Thanks very much for your help.

You need an `ALTER TABLE` query to add the column. I don't see how you're calculating the values for it, though. — , Nov 29 '14 at 02:09
possible duplicate of [How to add new column to MYSQL table](http://stackoverflow.com/questions/16113570/how-to-add-new-column-to-mysql-table) — , Nov 29 '14 at 02:09
Do you actually want to change the structure of your existing table by adding a new column or simply have it calculated in the query result? — PM 77-1, Nov 29 '14 at 02:12
@Hobo Sapiens, I already know how to insert a new column into a table (ALTER TABLE, as you mentioned). That is not the main objective. — sm535, Nov 29 '14 at 18:40
@PM77-1, I would like to add a new column. But that is secondary - I want to have the calculation of new_price done. That's what is tripping me. Thanks very much. — sm535, Nov 29 '14 at 18:44

score 1 · Accepted Answer · answered Nov 29 '14 at 02:27

I think the easiest approach is to use variables:

  select t.*,
         (@price := if(@i = item, @price,
                       if(@i := item, price, price)
                      )
         ) as new_price
  from table1 t cross join
       (select @i := '', @price := -1) vars
  order by item, count desc;

If you actually want to update values in the table, you can fit this into an update as well:

update table1 t join
       (select t.*,
               (@price := if(@i = item, @price,
                             if(@i := item, price, price)
                            )
               ) as new_price
        from table1 t cross join
             (select @i := '', @price := -1) vars
        order by item, count desc
       ) tp
       on tp.item = t.item and tp.price = t.price and tp.count = t.count
    set t.new_price = tp.price;

thanks very much. Testing your suggestion (the 'update' part of the code is what I need). — sm535, Nov 29 '14 at 18:42

Insert a new column based on existing column values

1 Answers1