SQL merge on in DB2 running on AS400, 6.1

Question

The following query does not work in DB2 which is running on the As400. I've read of similar issues but am unsure how to modify my query accordingly.

merge into AB27PR AB
    using (select USER, ROCOMP,REFID, QREADSTAT
             from S490JR
            where QREADSTAT =1) SR
    on
       (AB.USER= SR.USER
        AND AB.XCCOMP = SR.ROCOMP
        AND AB.XEFID = SR.REFID )
    when matched 
     and AB.XREADSTAT = 0
    then update set XREADSTAT = SR.QREADSTAT;

Answer 1

Since it's now known to be 6.1, we know that MERGE isn't appropriate and a straight UPDATE could be used. Simplest might be:

update AB27PR AB
   set AB.XREADSTAT = 1
where AB.XREADSTAT = 0
  and exists( select SR.QREADSTAT
                from S490JR SR
                where AB.USER = SR.USER
                  AND AB.XCCOMP = SR.ROCOMP
                  AND AB.XEFID = SR.REFID
                  AND SR.QREADSTAT = 1 )

Since AB.XREADSTAT will always receive a (1) value from table S490JR, it can be supplied as a constant. The only requirement is that a properly matching row should 'exist' in S490JR.

A more general UPDATE that would cover conditions that aren't needed here could look like:

update AB27PR AB
   set AB.XREADSTAT = ( select max( SR.QREADSTAT )
                          from S490JR SR
                          where AB.USER = SR.USER
                            AND AB.XCCOMP = SR.ROCOMP
                            AND AB.XEFID = SR.REFID
                            AND SR.QREADSTAT = 1 )
where AB.XREADSTAT = 0
  and exists( select SR.QREADSTAT
                from S490JR SR
                where AB.USER = SR.USER
                  AND AB.XCCOMP = SR.ROCOMP
                  AND AB.XEFID = SR.REFID
                  AND SR.QREADSTAT = 1 )

In that case, it pulls whatever value is in SR.QREADSTAT. Of course, since the WHERE clause limits the value to (1), it's still the only possible result for the SET clause.

Also, the MAX() function is used in order to handle the possibility that there might be multiple rows in S490JR that satisfy the WHERE conditions. (We don't know what every row in your table contains.) The result set of the sub-select of the SET clause can only contain a single row. Only a single value can fit into the SET column. The MAX() function ensures a single value even though that value might be in multiple matching rows. The MIN() function could also be used in place of MAX(). (I'd like SQL to have something like an ANY() function, but the SQL standard used for 6.1 simply has nothing like that.)

Note that in both examples the subselect needs to condition the WHERE clause of the UPDATE statement itself. You want to be sure to update only "matching" rows.