Trying to update one file based on a match in another file

[LucyB]

I am trying to update one field on records in a database file, but only the database file records for which there is a matching record in a small upload file. The upload file contains one field, item number, and is a list of items to be deleted. The database file is our ERP system's Item Master. Thank goodness for test libraries!

Select * from TestLib/MasterFile inner join MyLib/DltList on MasterFile.itemno=DltList.itemno

The above SQL statement, when run on the iSeries successfully selects from MasterFile only the 200+ item records that are also in the upload file, DltList.
Sadly, when I try to add the update function...

UPDATE TestLib/MasterFile
SET RecID = 'IZ'
WHERE EXISTS (Select * from TestLib/MasterFile inner join MyLib/DltList on MasterFile.itemno=DltList.itemno)

...I end up updating all records in the master file. Clearly, I'm misunderstanding something. What am I missing?

TIA,
Lucy

[jj_dahlheimer]

Re: Trying to update one file based on a match in another file

You want WHERE EXISTS (Select * from MyLib/DltList on MasterFile.itemno=DltList.itemno)

WHERE EXISTS (Select * from TestLib/MasterFile inner join MyLib/DltList on MasterFile.itemno=DltList.itemno) Will always return something hence updating every record in your master file.

When I write statements that will be meant for updating I always make sure they work as expected using a select first.

select * from TestLib/MasterFile
WHERE EXISTS (Select * from TestLib/MasterFile inner join MyLib/DltList on MasterFile.itemno=DltList.itemno)

Will return every record from the masterfile.

[Rocky]

Re: Trying to update one file based on a match in another file

You're tying the two files ok with the subselect - but you're not tying that with the file in the update.

[COLOR=#333333]UPDATE TestLib/MasterFile a[/COLOR]
[COLOR=#333333]SET RecID = 'IZ' [/COLOR]
[COLOR=#333333]WHERE EXISTS (Select * from TestLib/MasterFile b inner join MyLib/DltList c on (b.itemno=c.itemno) where a.itemno=b.itemno) [/COLOR]

You have to verify that the record being considered for update is found in the joined file... or you could simplify it.

[COLOR=#333333]UPDATE TestLib/MasterFile a[/COLOR]
[COLOR=#333333]SET RecID = 'IZ' [/COLOR]
[COLOR=#333333]WHERE EXISTS (Select * from MyLib/DltList b where a.itemno=b.itemno) [/COLOR]

Since you're wanting to update Masterfile if the record exists in dltlist - the above should accomplish that.

[LucyB]

Re: Trying to update one file based on a match in another file

Ah ha! Rocky, thank you. The need to link the update clause to the sub-select makes sense to me. I thougth using WHERE EXISTS would accomplish that, but clearly not. Your suggested code makes sense to me, and it worked just as I needed it to.

jj_dahlheimer, thank you as well. As it happens, I had already tried reversing the order of the 2 files in the su-bselect, based on reading the IBM SQL manual. Unfortunately, that approach still caused update of all records in the master file.

One step further on the road to using SQL.