Igor Tandetnik wrote:
>
> Barry1337 <jmichiels...@gmail.com> wrote:
>> 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.
>
> Try this:
>
> update STAYSPEC set date_out = coalesce(
> (select date_in from STAYSPEC ss2
> where ss2.staynum = STAYSPEC.staynum and
> ss2.order_spec = STAYSPEC.order_spec + 1),
> (select date_out from STAYHOSP sh where sh.staynum = STAYSPEC.staynum)
> );
>
>> Whenever I execute the above query it doesn't give an error or something,
>> it's just keeps running without ever stopping !
>
> Make sure you have an index on STAYSPEC(staynum, order_spec) (or at least
> on STAYSPEC(staynum) ), as well as one on STAYHOSP(staynum)
>
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
Thanks! You made my day :) Apparantly the indexing stopped the infinite
querying. Could you please explain me why the indexing is so important? (for
future reference).
--
View this message in context:
http://old.nabble.com/UPDATE-question-tp32673794p32677141.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
