Re: update with from

On Monday, January 23, 2012 7:32:35 am Sim Zacks wrote:
> 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
> )

From what I can see they are not the same queries, notwithstanding the
selectivity in the second query.  In fact I am not sure what the second query
accomplishes that cannot be done in the first query:)

Would you not get the same result in the first query by doing something like:

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 limit 1




--
Adrian Klaver
adrian.klaver@gmail.com