Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: UPDATE question PLEASE HELP

    I have the following query:

    Code:
    UPDATE STAYSPEC SET date_out = 
    CASE WHEN EXISTS 
         (SELECT * 
          FROM STAYSPEC AS STAYSPEC2 
          WHERE (STAYSPEC2.STAYNUM = STAYSPEC.STAYNUM) AND 
         (STAYSPEC2.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1)) 
    THEN 
         (SELECT date_in FROM STAYSPEC AS STAYSPEC3 
          WHERE (STAYSPEC3.STAYNUM = STAYSPEC.STAYNUM) 
          AND (STAYSPEC3.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1))
    ELSE 
          (SELECT STAYHOSP.date_out FROM STAYHOSP 
          WHERE (STAYHOSP.STAYNUM = STAYSPEC.STAYNUM)) 
    END;
    date_in and date_out have both the type TEXT.

    So I need, for every record in STAYSPEC, to find another record in STAYSPEC that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I want to replace the date_out from STAYSPEC with that date (in text format).

    If such a record does not exist (EXISTS) then it needs to take the date_out from the STAYHOSP table where the STAYNUM is the same.

    Whenever I execute the above query it doesn't give an error or something, it's just keeps running without ever stopping ! Since it's quite hard to debug this I sincerely hope someone can help me with this.
    Last edited by Barry1337; 10-18-11 at 07:46.

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by Barry1337 View Post
    Whenever I execute the above query it doesn't give an error or something, it's just keeps running without ever stopping ! Since it's quite hard to debug this I sincerely hope someone can help me with this.
    Any triggers involved causing a loop?

    Otherwise, an UPDATE statement is always expected to finish. Then it seems like a bug in your dbms product.

    Have you tried executing the UPDATE with just a few rows in each table?

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Since the statement does not include a WHERE clause for the UPDATE itself (just in the nested subselects), you will UPDATE _every_ row in the table.

    How many rows are there? Maybe it is just taking so long due to the data volume?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Oct 2011
    Posts
    3
    The answer apparantly was "Make sure you have an index on STAYSPEC(staynum, order_spec) (or at least on STAYSPEC(staynum) ), as well as one on STAYHOSP(staynum) ". Thanks for the suggestions though

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An equivalent UPDATE statement may be...

    Code:
    UPDATE STAYSPEC AS ss
       SET date_out =
           COALESCE(
              (SELECT ss2.date_in
                FROM  STAYSPEC AS ss2
                WHERE ss2.STAYNUM    = ss.STAYNUM
                  AND ss2.ORDER_SPEC = ss.ORDER_SPEC + 1
              )
            , (SELECT sh.date_out
                FROM  STAYHOSP AS sh
                WHERE sh.STAYNUM = ss.STAYNUM
              )
           )
    ;
    Mimer SQL Developers - Mimer SQL-2003 Validator

    Code:
    Result:
    
    Conforms to Core SQL-2003

Posting Permissions

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