I am trying to update some values in a table based on a translation table that I have built. I have tried two queries, one whose syntax will not work, and one with undesirable effects.

1)
UPDATE DEVDTA.LN_LEASERECORD
SET DOCO = PRODDTA.TBL_LEASECOPY.NEWDOCO
WHERE DEVDTA.LN_LEASERECORD.DOCO = PRODDTA.TBL_LEASECOPY.OLDDOCO;

This is exactly what I would like the query to do...it is pretty self expanatory...however PRODDTA.TBL_LEASECOPY.NEWDOCO errors out as an undefined column.

2)
UPDATE DEVDTA.LN_LEASERECORD
SET DOCO = (SELECT NEWDOCO FROM PRODDTA.TBL_LEASECOPY WHERE PRODDTA.TBL_LEASECOPY.OLDDOCO = DEVDTA.LN_LEASERECORD.DOCO);

This runs however where the select by itself only selects matching records, in the update query, the 226 matching records are correctly updated will 8900+ records are changed to null.

Can anyone tell me what I need to do differently?

Thanks in advance.

Ryan Hunt

Try adding
WHERE DEVDTA.LN_LEASERECORD.DOCO IN (SELECT OLDDOCO FROM PRODDTA.TBL_LEASECOPY)

See:

http://www.dbforums.com/showthread.p...52#post3689852

Newbie

That was simple...I feel rather silly...

Thanks. RH