There isn't much in it, but it looks the one with IFNULL is the fastest. Will stick to that one.
RBS -----Original Message----- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 21:17 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Re: how to do this case when? Thanks; I came up with number 3, but I like your number 1. Any idea what could be the fastest or will it all be the same? RBS -----Original Message----- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 20:59 To: SQLite Subject: [sqlite] Re: how to do this case when? RB Smissaert <[EMAIL PROTECTED]> wrote: > How do I alter this SQL, so that the original field remains the same > when > there is no match? A case when else end should do it, but I can't get > it > right. > > UPDATE Table1 SET Field1 = > (SELECT Field2 FROM Table2 > WHERE Table1.Field1 = Table2.Field1) UPDATE Table1 SET Field1 = IFNULL( (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1), Field1); -- or UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1 union all select Table1.Field1; ); -- or UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) where exists (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) Igor Tandetnik ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
