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?

Evident

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.

Evident

Please flag an answer as the answer so this question gets removed from the list of unanswered questions :) – Vuong

@Frans: I had to wait 48 hours before I could do so, stack-overflow rules :) – Evident

Nice. Thanks for taking time to provide a full example! – Penzance

got the same issue, thanks! i solved it with the inner join solution – Vex

do you think you could help with my problem? it seems to be a similar issue. tia! #17071002 – Reel

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 ;)

Vuong

Thanks, I will try it as soon as I can. And by the way - I did RTFM and tried everything that made sense before posting the question :) – Evident

thanks, the manual entry did clearify me the solution. positive for you also – Vex

Answer 3

This is how it is done in mssql, I think mysql is the same or similar:

create table T (id int, ratio float, appearances int)
insert T values (1, null, 2)
insert T values (1, null, 3)

update T
set ratio = cast(appearances as float)/ agg.appearancesSum
from T join (
    select id, sum(appearances) as appearancesSum
    from T
    group by id
) as agg on t.id = agg.id

Jannette

Sorry, this does not work in MySQL. I'm not down-voting it since I'm sure it works on mssql... – Evident

Answer 4

This is an old one but I think people are still looking this over. I looked at the preferred answer, but continued to work the correlated subquery for my MariaDB/MySQL database. Correlated subqueries are my least favorite to put together as they always make sense in my head till I try to configure one. lol

I tried several flavors a correlated subquery that the updated all the records in the table or all with same common key.

What ended up working was adding another "where exists select" on the target table.

I am updating 2 encrypted records that got corrupted from a record on the same table that has a valid encrypted field. A cut and paste into a standard update statement did not work.

Depending on the number of rows it updated I ran the roll back or commit, Until I got a subquery that updated the expect 2 rows

start transaction;

update account tar
   set value =  (select value 
                 from account src
                 Where src.name = tar.name
                   and src.name = 'acct_no'
                   and src.customAttributesId = '8adac1537dbe5c39017dc89f082e0341') 
    Where exists (select 1
                  from account
                  where tar.name = 'acct_no'
                   and tar.customAttributesId in 
                 ('8adac1537dbe5c39017dc8a0e5db0610', 
                  '8adac1537dbe5c39017dc8a0e491060d')
                );

Rollback;
Commit;

15:43:16    update account tar    set value =  (select value                   from)    2 row(s) affected Rows matched: 2  Changed: 2  Warnings: 0  0.141 sec

I have another process that confirmed the encrypted Account number in the updated field was valid. Hope this helps.

Epencephalon