Because your query was fairly complex, to help everyone debug it, I
simplified it using simple names (tables are a, b, c; fields are m, n, x, y,
z) and much simpler use of whitespace / indentation:
UPDATE a
SET x = CASE
WHEN EXISTS
(
SELECT *
FROM a
AS b
WHERE b.n = a.n
AND b.m = a.m + 1
)
THEN
(
SELECT y
FROM a
AS c
WHERE c.n = a.n
AND c.m = a.m + 1
)
ELSE
(
SELECT d.z
FROM d
WHERE d.n = a.n
)
END;
Note that the name of field 'z' (from table 'd') happens to be the same as
the name of field 'x' (from table 'a'), but since they were from different
tables, I chose different names above - obviously you'll need to translate
everything back to your names before any advice can be useful.
So that's it - I did this to help others debug your problem. However I will
offer an observation which may (or may not) help: You might want to try
using a qualified name in the SELECT within the THEN clause ("SELECT y"
rather than "SELECT c.y"). I'm not sure if a qualified name would help in
the SET clause, but you could also try that.
Marshall
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Barry1337
Sent: Tuesday, October 18, 2011 6:04 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] UPDATE question
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.
--
View this message in context:
http://old.nabble.com/UPDATE-question-tp32673794p32673794.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
