Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012

    Unanswered: MIN() function in the WHERE clause with the "FOR UPDATE" clause

    Good morning

    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.

    select *
    where SRC_SYS_ID = 1
    and BNK_NB = 222
    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.

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    Let's consider what would happen if you'd do a positioned update like this:
    SET ACCT_nb_ts = '9999-12-31'
    WHERE CURRENT OF <cursor>
    The UPDATE would (obviously) influence the result of the query. Fetching through the cursor wouldn't work any longer because of the update, which actually changes the query results.

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts