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]
-----------------------------------------------------------------------------
