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

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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s