CODEWITHSUNDEEP
mysqlinsert
sm535
sm535

Reputation: 587

Insert a new column based on existing column values

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.

Upvotes: 1

Views: 1883

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 1

Related Questions