1

There is a similar question at here., but that one seems to be too old as the answer was provided back in 2009 and receives only seven supports. I believe there is a better strategy.

The table is as follows:

id   weight  cost
1    1.2     0
2    2.5     0
3    2.9     0
...

where the weight and cost are the weight and cost of an item. Now I want to update the cost of each id by giving a total cost N. The cost of each id is a fraction of the total cost based on its relative weight respect to the total weight.

Take a table with only three rows as above, the total weight is 1.2+2.5+2.9=6.6, therefore, the costs of ids 1, 2 and 3 are: 

N*1.2/6.6
N*2.5/6.6
N*2.9/6.6

Is there a way for me to do this update using a single query? I tried something but it gives me the error message SQLSTATE[HY000]: General error: 1111 Invalid use of group function; here is my code and I am wondering why exactly it cannot be done this way:

UPDATE `table`
    SET `cost` = `cost` + :total_cost * `weight`/SUM(`weight`)
WHERE 1
Community
  • 1
  • 1
Dainy
  • 89
  • 9
  • @TimBiegeleisen, thanks for pointing out. But that one seems to be too old as the answer was provided back in 2009 and receives only seven supports. I believe there is a better answer. – Dainy Jul 20 '15 at 04:48
  • Has MySQL changed its restriction from doing a self-join during an `UPDATE`? If so, then you have another option. – Tim Biegeleisen Jul 20 '15 at 04:49

2 Answers2

2
create table tbl1
(
id int auto_increment primary key,
weight decimal(10,4) not null,
cost decimal(10,4) not null
);

insert tbl1(weight,cost) values (1.2,0),(2.5,0),(2.9,0),(8.1,0),(1.1,0),(1.2,0);

-- assume total shipping cost (N) = 17.20
update tbl1 t1,
(select sum(weight) as tot_weight from tbl1) t2
set cost=17.20*(t1.weight/t2.tot_weight)

select * from tbl1;

select sum(cost) from tbl1;
-- 17.1999 (close enough :>)
Drew
  • 24,851
  • 10
  • 43
  • 78
0

Like stated here, the typical way to handle this situation is to do a multi-table update. You should cross join to a subquery that calculates the sum of the weights : 

UPDATE 
    tableName tableNameToUpdate
CROSS JOIN
   (SELECT 
      SUM(innerTableName.weight) AS sumWeight
    FROM 
      tableName AS innerTableName
    ) tableNameSub
SET 
   tableNameToUpdate.cost = :total_cost * 
                             (tableNameToUpdate.weight / tableNameSub.sumWeight)

In practice, if you want to do a cross join, then use cross join. It is much better than:

from A, B
Community
  • 1
  • 1
AlexB
  • 3,518
  • 4
  • 29
  • 46