OK, thanks again.
-----Original Message-----
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 20, 2007 10:56 AM
To: SQLite
Subject: [sqlite] Re: Re: Looking for equivalent syntax
Anderson, James H (IT)
<Jim.Anderson-/PgpppG8B+R7qynMiXIxWgC/[EMAIL PROTECTED]> wrote:
> In order to improve my understanding, I'd like to ask 2 questions re
> the
> sql, below.
>
> 1. what is the relationship between the "select * from where" within
> the
> "where exists" and the "select yadayadayada from where" within the
> set?
No direct relationship. However, the conditions in the two selects are
designed to be similar enough, so that when EXISTS test succeeds by
finding a suitable row, the select in SET would extract a field from
that same row.
> 2. why is it not necessary to include the "b.CDEvent=c.CDEvent" which
> is
> present in the "where exists" in the "where" within the set?
Now that I think of it, it might be necessary to join to tmp_events in
the SET clause after all. Suppse the data looks like this:
select CDId from C1_credDerivEvent;
1
select CDId, CDEvent from C1_tmp_credDerivEvent;
1 10
1 20
select CDEvent from tmp_events;
20
Here a select that uses all three tables would produce 20. A select that
omits tmp_events would produce two records with the values 10 and 20,
from which SQLite would just pick the first one. This could be 10,
giving a wrong answer.
So to be on the safe side, make it
update C1_credDerivEvent
set CDEvent = CDEvent || ',' ||
(select b.CDEvent
from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent)
where exists (
select * from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)
Igor Tandetnik
------------------------------------------------------------------------
-----
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
-----
--------------------------------------------------------
NOTICE: If received in error, please destroy and notify sender. Sender does not
intend to waive confidentiality or privilege. Use of this email is prohibited
when received in error.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
