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:

>
> 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.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com