MySQL/SQL: Update with correlated subquery from the updated table itself

Question

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?

Answer 1

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.

Answer 2

Use joins right after UPDATE: Reference Manual – 13.2.11 UPDATE Syntax

so UPDATE table1 inner join table2 on .... set table1.foo=value where table2.bla = someothervalue

With these kind of things, always look at the manual. MySql has a proper reference manual, so it shouldn't be that hard to get the right syntax ;)