Re: table alias on update, another update question

On Mon, Jun 23, 2003 at 21:31:01 -0500,
nolan(at)celery(dot)tssi(dot)com wrote:
>
> I need to update several columns in table1 with either the minimum or
> maximum value of a corresponding column in a matching set of rows in
> table2. I'd like to be able to do it in a single query.
>
> The following doesn't work, it updates just one row.
>
> UPDATE table1
> set t1_col1 = max(t2_col1),
> t1_col2 = min(t2_col2)
> from table2
> where t1_key = t2_key
>
> I don't seem to be able to use a group clause.
>
> Is there a way to write this query?

I think something like the following is best. I rewrote the max and min
functions in a way that will work well if there is a combined index on
t2_key and t2_col1 as well as t2_key and t2_col2. Another approach would
be to join to a group by select from table2. There may be some circumstances
where that gives you better performance, but I can't think of a particular
circumstance where that is likely offhand.

UPDATE table1
set t1_col1 =
(select t2_col1 from table2 where t1_key = t2_key
order by t2_key desc, t2_col1 desc limit 1),
set t1_col2 =
(select t2_col2 from table2 where t1_key = t2_key
order by t2_key, t2_col2 limit 1);