08-28-12, 13:20 #1Registered User
- Join Date
- Aug 2012
Unanswered: MIN() function in the WHERE clause with the "FOR UPDATE" clause
Just read what I need to include.
Error code = SQL0118N The table or view that is the target of the INSERT, DELETE, or UPDATE statement is also specified in a FROM clause.
Version of DB2 = 9
I have a database with 100's for rows with new account number ranges. I have a time stamp field that I have to use to get the oldest record and get the next account number. The following SQL works fine without the FOR UPDATE clause, any ideas on how to get this to work properly? I want just one row back and ensure that no one else can update that record while I am in that row.
where SRC_SYS_ID = 1
and BNK_NB = 222
and EXPIRED_ACCT_NB_IN IS NULL
and ACCT_NB_TS = (select min(acct_nb_ts) from MQS.New_ACCT_NB_GAPS) for update
Last edited by Gus_kc; 08-28-12 at 13:23.
08-28-12, 21:32 #2Registered User
- Join Date
- Jan 2007
- Jena, Germany
Let's consider what would happen if you'd do a positioned update like this:
UPDATE MQS.NEW_ACCT_NB_GAPS SET ACCT_nb_ts = '9999-12-31' WHERE CURRENT OF <cursor>
What you can try is "FOR UPDATE OF <column-name>". Alternatively, forget about the incrementing the account numbers. Use a sequence or an identity column instead (and ignore the potential for gaps you have with either approach).Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development