MySQL: Updating sets of columns

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 = 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.

1 Comment

Filed under MySQL

One response to “MySQL: Updating sets of columns

  1. A hack is to send back a CSV string from one subselect and parse it:

    mysql> select substring_index(x,’,’,1) a, substr(substring_index(x,’,’,2),instr(substring_index(x,’,’,2),”,”)+1) b, substring_index(x,’,’,-1) c from (select concat_ws(‘,’, ‘x’, ‘y’, ‘z’) x) t;

    | a | b | c |
    | x | y | z |

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s