Hello, all.

I hope someone can help with this issue I have been fighting for a while.

I have the following SQL statement that SHOULD NOT update any rows:


UPDATE PS_PO_HDR_S_WK_T4
SET PO_AMT_APPR_NEEDED = 'Y'
WHERE PROCESS_INSTANCE = 999999
AND PO_STATUS IN ('A','O','PA', 'D')
AND PO_AMT_APPR_ND_BU = 'Y'
AND NOT EXISTS ( SELECT 'X' FROM PS_POBUILD_APPR POB
WHERE POB.BUSINESS_UNIT =
PS_PO_HDR_S_WK_T4.BUSINESS_UNIT AND POB.PO_PROCESS_ID =
PS_PO_HDR_S_WK_T4.PO_PROCESS_ID AND POB.PO_AMT_APPR_NEEDED = 'N')


Here is the thing. Table PS_PO_HDR_S_WK_T4 contains two rows with identical values for fields:
- PROCESS_INSTANCE
- PO_STATUS
- PO_AMT_APPR_ND_BU
- BUSINESS_UNIT
- PO_PROCESS_ID

Which means, both rows would match (or not) the criteria just the same.

This UPDATE should not update ANY rows. I know this becuase if I run this select, no rows are returned (which is what I expect):


SELECT * FROM PS_PO_HDR_S_WK_T4
WHERE PROCESS_INSTANCE = 999999
AND PO_STATUS IN ('A','O','PA', 'D')
AND PO_AMT_APPR_ND_BU = 'Y'
AND NOT EXISTS ( SELECT 'X' FROM PS_POBUILD_APPR POB
WHERE POB.BUSINESS_UNIT =
PS_PO_HDR_S_WK_T4.BUSINESS_UNIT AND POB.PO_PROCESS_ID =
PS_PO_HDR_S_WK_T4.PO_PROCESS_ID AND POB.PO_AMT_APPR_NEEDED = 'N')


But the SQL Update is updating one of the two rows!!!

If the PS_PO_HDR_S_WK_T4 similar to these two, the SELECT will still return zero rows and the UPDATE will update 1 row.

Can anyone see ANYTHING I am not seeing?

Thanks a lot

How did you found the updated row?

which db2 or what release?

it sounds like a bug and you should open a PMR to IBM.

can you provide the DDL and data? it looks like a very interesting problem.

By querying the updated table. Only one of the two rows got its PO_AMT_APPR_NEEDED updated to "Y"

I need to get back in the system and find that information.

I will post it as soon as I can but it might not be until tomorrow.

It is indeed an interesting problem that has been killing me for the past two days.

I suspected that the cause of your issue might be one of the followings.

1) Bug of DB2.

2) One of the two rows was PO_AMT_APPR_NEEDED = 'Y' before update.
Because there was no condition "PS_PO_HDR_S_WK_T4.PO_AMT_APPR_NEEDED <> 'Y'" in your select.
And PO_AMT_APPR_NEEDED was not included in the identical values for fields.

Thing is the SQL Update returns a "1 Row Updated message". So it does update the row.

You must be using some third party software to run your statements, thus introducing an extra source of possible errors. I would try to run the statements in the DB2 CLP.