Re: update with from

On 01/23/2012 07:10 PM, Adrian Klaver wrote:
> On Monday, January 23, 2012 7:32:35 am Sim Zacks wrote:
>> On 01/23/2012 05:13 PM, Adrian Klaver wrote:
>
>>
>> When I throw in code to make the select only return the correct rows
>> The select statement takes 9 secs by itself:
>> select a.partid,a.deliverywks
>> from poparts a where popartid in (
>> select b.popartid from poparts b
>> join pos c using(poid)
>> join stock.lastrfqdateperpart d using(partid)
>> where c.isrfq and c.issuedate > d.issuedate-7
>> AND b.unitprice > 0::numeric AND b.quantity >= 100::numeric AND
>> c.postatusid = ANY (ARRAY[40, 41])
>> and b.partid=a.partid
>> order by b.partid,b.unitprice, b.deliverywks
>> limit 1
>> )
>
> To clarify what I posted earlier, my suggestion was based on rewriting the
> second query as:
>
> select b.partid,b.deliverywks b.popartid from poparts b
> join pos c using(poid)
> join stock.lastrfqdateperpart d using(partid)
> where c.isrfq and c.issuedate > d.issuedate-7
> AND b.unitprice > 0::numeric AND b.quantity >= 100::numeric AND
> c.postatusid = ANY (ARRAY[40, 41])
> order by b.partid,b.unitprice, b.deliverywks
> limit 1
>
> I may be missing the intent of your original query, but I think the above gets
> to the same result without the IN.
>

My first query returns all rows of each part ordered such so that the
row I want to actually update the table with is last. This query returns
12000 rows, for the 600 parts I want to update.

My second query with the limit within the subselect gets 1 row per part.
This returns 600 rows, 1 row for each part I want to update.

Your suggestion would only return one row.

See
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Select_first_n_rows_from_group
for reference.