Thanks, I'll try both and see which is faster.
-----Original Message-----
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Monday, February 19, 2007 2:33 PM
To: SQLite
Subject: [sqlite] Re: Looking for equivalent syntax
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]
------------------------------------------------------------------------
-----
--------------------------------------------------------
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]
-----------------------------------------------------------------------------
