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

    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 *
    from MQS.NEW_ACCT_NB_GAPS
    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Let's consider what would happen if you'd do a positioned update like this:
    Code:
    UPDATE MQS.NEW_ACCT_NB_GAPS
    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
  •