> 2. I suggest that you're better off doing the logic entirely in SQL, rather
> than application code, for the sake of portability, data integrity and speed.
I'd say this is a very bad advice for the developer using SQLite.
First of all "insert or ignore" and "insert or replace" are not
portable SQL structures, so we can forget about portability. Secondly
SQL and application are completely separated creatures only in case of
some server-side RDBMS. In case of SQLite they are the same
application and if you add complexity to SQL, you add complexity to
your application and it happens very often that complexity on SQL side
is much worse in performance than some additional application bits on
the other side. Just test different approaches and see it for
yourself.
Pavel
On Mon, Oct 18, 2010 at 7:54 PM, BareFeetWare <list....@tandb.com.au> wrote:
> On 19/10/2010, at 8:10 AM, NSRT Mail account. wrote:
>
>> I would use the update if I knew the entry already existed. In my
>> application however, it doesn't know if the entry already exists. I was
>> looking for something to replace MySQL's ON DUPLICATE KEY UPDATE.
>>
>> I modified my application to use two SQL statements instead.
>>
>> if (!db.execute("INSERT INTO users VALUES(?, ?, ?);", user.id,
>> user.type, user.name))
>> {
>> db.execute("UPDATE users SET name=? WHERE id=? AND type=?;",
>> user.name, user.id, user.type);
>> }
>
> A few points:
>
> 1. I know the frustration with "replace". Unfortunately, as Igor pointed out,
> it deletes the old row and inserts a new one. It would be great to a have a
> "insert or update" command, but we don't, so you have to code it explicitly
> as "insert or ignore" and an "update".
>
> 2. I suggest that you're better off doing the logic entirely in SQL, rather
> than application code, for the sake of portability, data integrity and speed.
>
> 3. The identifiers (table and column names) should be wrapped in double
> quotes not single quotes (which is for string literals). Most of the time
> single quotes will work by default, but not always.
>
> -- Setting up your initial data:
>
> insert or ignore into users ("id", "type", "name") values (1, 4, 'Joe');
>
> -- now your new data, by inserting a new id if doesn't already exist, then
> updating the columns:
>
> insert or ignore into users ("id") values (1);
> update users set "name" = 'Joe C', "type" = 4, where "id" = 1;
>
> -- alternatively you could do this, which will update the existing row, if
> exists, or insert a new one if it doesn't:
>
> update users set "name" = 'Joe C', "type" = 4, where "id" = 1;
> insert or ignore into users ("id", "type", "name") values (1, 4, 'Joe C');
>
> Furthermore, I suspect that you want the userId to be dynamically looked up,
> rather than specifically entered. So you may want something like this:
>
> insert into "meetings" ("userId", "type", "password")
> select
> (select id from users where name = 'Joe C' and type = 4)
> , 4
> , 'blah'
> ;
>
> HTH,
> Tom
> BareFeetWare
>
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
