Hi,

I have this update statement and I am sure there has to be a way to make it more efficient. It currently works but I would like to find a way to do it with only 1 select statement rather than 2. Is this possible?

UPDATE KELLYP.LOSTSALE A
SET A.UNFMT = (SELECT SUNFMT FROM KELLYP.INSMFHTEST B
WHERE A.LSPART = B.SPART AND A.LSLNCD = B.SFRAN)
where exists (select sunfmt
from kellyp.insmfhtest b
where a.lspart = b.spart and a.lslncd = b.sfran);

I'm not sure I understand your criteria of "efficiency", but consider using the MERGE statement.

Well it just seems like there should be a better way. Using 2 select statements is basically having to go through the whole table twice. I will look into MERGE but I am on IBM i and I am not sure that MERGE is supported but I will do some more research. Thanks for the response.

This isn't necessarily the case, generally speaking, but then I don't know how the DB2 query optimizer works. You may want to look at the access plan.

Maybe add a unique index on KELLYP.INSMFHTEST(SPART ,SFRAN)
will speed your query。

Can you provide the cost of this query and also the access plan.. from db2expln. Only then it will be easier to tune this sql... either by adding indexes.. or checking the nlevels of existing indexes... or my rewriting the sql.

*by rewriting the sql ..