UPDATE question PLEASE HELP

Barry1337 · #1 (**permalink**) 10-18-11, 06:42

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.

JarlH · #2 (**permalink**) 10-18-11, 08:49

Quote:

Originally Posted by Barry1337

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?

stolze · #3 (**permalink**) 10-18-11, 13:50

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?

Barry1337 · #4 (**permalink**) 10-18-11, 16:31

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

tonkuma · #5 (**permalink**) 02-07-12, 07:36

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

Internet Services

Web Development

Digital Marketing

Consumer Tech