On 2/19/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:
I'm trying to convert the following statement in Sybase syntax into the
equivalent sqlite syntax:
update C1_credDerivEvent
set a.CDEvent = a.CDEvent || ',' || b.CDEvent
from C1_credDerivEvent a,
C1_tmp_credDerivEvent b,
tmp_events c
where a.CDId = b.CDId
and b.CDEvent = c.CDEvent
Here's what I came up with but it turns out not to be equivalent at all:
insert or replace into C1_credDerivEvent
select A.CDId as CDId,
A.CDEvent || ',' || B.CDEvent as CDEvent
from C1_credDerivEvent A,
C1_tmp_credDerivEvent B,
tmp_events C
where A.CDId = B.CDId
and B.CDEvent = C.CDEvent;
The Sybase statement simply updates each record for which the where
clause is satified, yielding the same number of rows in the table before
the update as after.
The Sqlite statement on the other hand, adds rows to the table.
well yes, you yourself are asking SQLite to "INSERT or REPLACE"... see
the INSERT part... that adds rows to the table.
In your Sybase version, you are only UPDATEin (equivalent to the
REPLACE part). SQLite is just doing what you are asking it to do.
What is wrong with your original statement? You never mentioned
whether that worked on not... did you try it? (listed again below)
update C1_credDerivEvent
set a.CDEvent = a.CDEvent || ',' || b.CDEvent
from C1_credDerivEvent a,
C1_tmp_credDerivEvent b,
tmp_events c
where a.CDId = b.CDId
and b.CDEvent = c.CDEvent
You could update it to the more standard-ish syntax like so
UPDATE C1_credDerivEvent
SET a.CDEvent = a.CDEvent || ',' || b.CDEvent
FROM C1_credDerivEvent a JOIN C1_tmp_credDerivEvent b ON
a.CDId = b.CDId JOIN tmp_events c ON b.CDEvent = c.CDEvent
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
