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

by

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.

More Related Contents:

  1. MySQL, update multiple tables with one query
  2. UPDATE multiple rows with different values in one query in MySQL
  3. Multiple Updates in MySQL
  4. mysql update column with value from another table
  5. update multiple rows using limit in mysql?
  6. What are differences between INSERT and UPDATE in MySQL?
  7. MySql – Way to update portion of a string?
  8. MySQL UPDATE syntax with multiple tables using WHERE clause
  9. The best way to remove value from SET field?
  10. Update MySQL with if condition
  11. Update multiple rows with multiple ‘where’ clauses for each individual row
  12. MySQL update statement to store ranking positions
  13. Error related to only_full_group_by when executing a query in MySql
  14. How to declare a variable in MySQL?
  15. MySQL Cannot Add Foreign Key Constraint
  16. Grouping into interval of 5 minutes within a time range
  17. SQL counting all rows instead of counting individual rows
  18. Convert timestamp to date in MySQL query
  19. Update a column value, replacing part of a string
  20. In MySQL, should I quote numbers or not?
  21. MySQL add days to a date
  22. mysql dynamic query in stored procedure
  23. Does the order of conditions in a WHERE clause affect MySQL performance?
  24. Difference between these two joining table approaches?
  25. MySQL query, MAX() + GROUP BY
  26. MySQL export into outfile : CSV escaping chars
  27. Simple Random Samples from a MySQL Sql database
  28. MySQL Select minimum/maximum among two (or more) given values
  29. Execute INSERT if table is empty?
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment