MySQL/SQL: Update with correlated subquery from the updated table itself
- by Roee Adler
I have a generic question that I will try to explain using an example.
Say I have a table with the fields: "id", "name", "category", "appearances" and "ratio"
The idea is that I have several items, each related to a single category and "appears" several times. The ratio field should include the percentage of each item's appearances out of the total number of appearances of items in the category.
In pseudo-code what I need is the following:
For each category
find the total sum of appearances for items related to it. For example it can be done with (select sum("appearances") from table group by category)
For each item
set the ratio value as the item's appearances divided by the sum found for the category above
Now I'm trying to achieve this with a single update query, but can't seem to do it. What I thought I should do is:
update Table T
set T.ratio = T.appearances /
(
select sum(S.appearances)
from Table S
where S.id = T.id
)
But MySQL does not accept the alias T in the update column, and I did not find other ways of achieving this.
Any ideas?
