From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | nolan(at)celery(dot)tssi(dot)com |
Cc: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: table alias on update, another update question |
Date: | 2003-06-24 15:21:05 |
Message-ID: | 20030624152105.GA12418@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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);
From | Date | Subject | |
---|---|---|---|
Next Message | nolan | 2003-06-24 15:31:33 | Re: [GENERAL] Documentation quality WAS: interesting |
Previous Message | Robert J. Sanford, Jr. | 2003-06-24 15:18:26 | Dump/Restore and sequence permissions |