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
