The original statement works fine in Sybase. I did not try it in Sqlite because my previous experience is that the update statement in Sqlite does not support the "from" clause. Am I missing something here?
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Monday, February 19, 2007 1:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Looking for equivalent syntax 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] ------------------------------------------------------------------------ ----- -------------------------------------------------------- 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] -----------------------------------------------------------------------------
