mysql 更新子表_MySQL / SQL：使用更新后的表本身中的相关子查询进行更新

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?

解决方案

Following the two answers I received (none of which was complete so I wrote my own), what I eventually did is as follows:

UPDATE Table AS target

INNER JOIN

(

select category, appearances_sum

from Table T inner join (

select category as cat, sum(appearances) as appearances_sum

from Table

group by cat

) as agg

where T.category = agg.cat

group by category

) as source

ON target.category = source.category

SET target.probability = target.appearances / source.appearances_sum

It works very quickly. I also tried with correlated subquery but it was much slower (orders of magnitude), so I'm sticking with the join.