I am trying to create an SQL statement that will update many records based on a file containing fewer records. I can easily update ALL records in the primary file but I really only want to update those that need to be updated. In the item master file the cost has been changed for some records. In the warehouse file I need to update those records that now have a cost that is different from the item master record. This SQL statement will update all records with no problem:
UPDATE warehouse a
set a.w_cost = (SELECT distinct b.m_cost FROM itemmast b)
where (a.w_item = b.m_item and a.w_comp = b.m_comp)
(It is a guarantee that there will be an item record in the master file to match the multiple item records in the warehouse file.)
I haven't been able to figure out how to add in the constraint that the update should only occur if the W_COST does not equal the M_COST. I've played with both SQLRPGLE and interactive SQL without success. Thanks for any help!