CODEWITHSUNDEEP
mysqlsqlsql-updatemysql-error-1093
LazNiko
LazNiko

Reputation: 2123

Update with percentage calculation on the same table

I have a table like this:

ItemID      PersonID  Score      Percentage
==========================================
1             1       10         [ = 10 / 10+30 = 25%]
1             2       30         [ = 30 / 10+30 = 75%]
2             1       20         [ = 20 / 20+40 = 33%]
2             2       40         [ = 40 / 20+40 = 67%]

The data on "Percentage" is not entered but is auto-calculated at certain time interval. The calculation is Percentage = Score / Total Score of the ItemID

Instead of using "SELECT-do the maths-UPDATE" method, I'm trying to write a single SQL to update the "Percentage".

What I tried is like this:

UPDATE tb_temp AS t1 
    SET t1.Percentage = 
        CEIL( t1.Score / 
            (SELECT SUM(t2.Score) FROM tb_temp AS t2 WHERE t2.ItemID = t1.ItemID)
        );

But it doesn't work.(Error Code : 1093 You can't specify target table 't1' for update in FROM clause).

Any idea?

Upvotes: 0

Views: 2225

Answers (2)

Pradeep Singh
Pradeep Singh

Reputation: 3634

UPDATE tb_temp AS t1 
    SET t1.Percentage = 
        CEIL( t1.Score / 
            (SELECT SUM(t2.Score) FROM tb_temp AS t2 left join tb_temp AS t3 on t2.ItemID = t3.ItemID)
        );

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

try:

  UPDATE tb_temp t1 
     JOIN (SELECT ItemId, SUM(t2.Score) TotalScore
           FROM tb_temp 
           group by ItemId) t2 ON t2.ItemID = t1.ItemID
  SET t1.Percentage = CEIL( t1.Score / t2.TotalScore)

Upvotes: -1

Related Questions