I was looking for updating a bunch of aggregate values from a child table into a master table. So, my requirement was something like
update parent_table set (minx,avgx,maxx) = (select min(x),avg(x),max(x) from child_table where parent_id = parent_table.id) where id = :parentid
I.e, ability to update multiple columns from the same subquery. However, MySQL currently doesn’t support this. I checked Oracle’s syntax, and found that it does have such syntax. In MySQL, this will have to be done by 3 separate subselects which will be 3 times more
If you know of making it work optimally for MySQL let me know. Ofcourse, if the outer clause is for only a single id, like the :parentid bind I had, then I could execute the subquery separately, pick the min, avg and max values and updated them directly. However, in my actual requirement, the parentid is not just a single id, but it itself comes from a subquery.