I am trying to create an update subquery that will update the table with the results of subquery within the table.
what I tried:
| Update VCSTSTNT.ACR B |
| Set B.REGION_NUMBER = A.REGION_NUMBER, B.REGION_OVRD_IND = 'Y', B.LAST_CHG_OPERATOR = 'BTCHREG', |
| B.LAST_CHANGE_DTE = CURRENT_DATE, B.TIME_STAMP = CURRENT_TIMESTAMP |
| WHERE B.AGENT_ID = ' 19699' |
| AND B.REGION_NUMBER NOT IN |
| (select A.REGION_NUMBER from VCSTSTNT.ACR A |
| where B.AGENT_ID = A.AGENT_ID |
| AND A.AGENT_ID = ' 19699' |
| AND A.REGION_OVRD_IND = 'Y' AND |
| A.TERMINATION_DTE = 99999999) |
| | |
| received error message: |
| S0022(-206)[IBM][CLI Driver][DB2/NT] SQL0206N "A.REGION_NUMBER" is not valid in the context where it is used. SQLSTATE=42703 |
| (0.00 secs) | |
any suggestions???!!!!!!!!!! Thanks!