Re: update with from

On 01/23/2012 05:13 PM, Adrian Klaver wrote:

>
> I guess the primary question here is, what are you trying to achieve?
> Do want a particular row to supply the values to the target table i.e the row
> with the most timestamp?
> What is the query you are using?
>
The query returns a partid, unitprice and delivery weeks from the latest
set of rfqs sent. I want to update the table with the delivery weeks per
part of the cheapest of those rfqs.

This is the update stmt I am using, assuming that it always updates the
table with the last row per part:
update stat_allocated_components a set
partarrivedate=current_date+(b.deliverywks*7),partarrivedate_source='RFQ
Est'
from
(select b.popartid,b.partid,b.unitprice,b.deliverywks from poparts b
    join pos c using(poid)
    join 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 desc, b.deliverywks desc) b
where a.partid=b.partid and partarrivedate is null and
a.stock-a.previouscommitmentlf+a.quantity<0 and b.deliverywks is not null

This query take 163 ms.

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
)