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(dot)klaver(at)gmail(dot)com