Re: SQL issue with EXISTS not "working"

On 23-Oct-2014 10:56 -0500, Darryl Freinkel wrote:

I need some help on this one.

I am merging 5 company records into 1 file and need to drop any
duplicates.

<<SNIP>>

What alternative ways are there to achieve this merge or what
mistake am I not seeing?

<<SNIP>>

The MERGE statement might be appropriate for the scenario.?

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/sqlp/rbafymerge.htm>
_Merging data_
"Use the MERGE statement to conditionally insert, update, or delete rows in a table or view.

You can use the MERGE statement to update a target table from another table, a derived table, or any other table-reference. This other table is called the source table. The simplest form of a source table is a list of values.
..."

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzmerge.htm>
_MERGE_
"The MERGE statement updates a target (a table or view) using data from a source (result of a table reference). Rows in the target that match the input data may be updated or deleted as specified, and rows that do not exist in the target may be inserted as specified. ...
..."


Or perhaps the CPYF command might be appropriate for the scenario.? If the required uniqueness is enforced with unique keys, then the Copy File (CPYF) command offers a Member Option (MBROPT) of Update\Add (*UPDADD) that, IIRC, replaces a duplicate row with the /same/ record in the From File (FROMFILE) member (FROMMBR).

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/dm/rbal3updadd.htm>
_Specifying *UPDADD when copying files_
"When you specify *UPDADD on the Copy File (CPYF) command, a from-file key value builds before the from-file record moves into the to-file. The from-file builds this key value by using the key specifications of the to-file.

Before the key value is built, the system performs any necessary field or data mapping, data conversion, or record selection. The system checks the to-file to see if this key value already exists in it (duplicate key of the from-file data). If the key value does exist in the to-file, the from-file record that contains the key value updates that to-file record.
The following rules apply if you specify MBROPT(*UPDADD) on the CPYF command:

• The to-file must be a local database physical file that contains a primary or unique key.
• ...
..."

FWiW: I did not spend much time reviewing the messages in this thread due to the apparent guessing, required presumably as a side effect of too little information having been provided about the scenario; no DDL nor constraints, and lack of any example data and both the improper results seen and the expected results.