Igor Tandetnik <[EMAIL PROTECTED]> wrote:
update C1_credDerivEvent
set CDEvent = CDEvent || ',' ||
(select b.CDEvent
from C1_tmp_credDerivEvent b
where C1_credDerivEvent.CDId = b.CDId)
where exists (
select * from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)
This one is shorter, and requires only one subselect per row:
update C1_credDerivEvent set CDEvent = ifnull(
CDEvent || ',' || (select b.CDEvent
from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId
and b.CDEvent=c.CDEvent),
CDEvent)
If the nested select produces an empty set, it will be treated as NULL,
which will force the concatenation to be NULL, and the update will
degenerate into a no-op (SET CDEvent=CDEvent).
It's not necessarily faster though. If SQLite can use indexes to satisfy
the WHERE clause in the first statement, it doesn't need to look at
every record. If only a small portion of all records actually needs
updating, the first query may run faster even though a second lookup is
necessary for those records that do get updated after all. The second
query requires a linear scan of C1_credDerivEvent table, and a lookup
for every record.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
