Re: [sqlite] INSERT OR UPDATE?

[BareFeetWare]

On 02/07/2011, at 4:54 AM, KeithB wrote:

> Is there any way to perform the equivalent of an INSERT OR UPDATE statement?
> 
> I have something like this:
> 
> CREATE TABLE t1 (id INTEGER PRIMARY KEY, value INTEGER);
> CREATE TABLE t2 (parent INTEGER REFERENCES t1 ON DELETE CASCADE, child
> INTEGER REFERENCES t1);
> INSERT INTO t1 VALUES(1,100);
> INSERT INTO t1 VALUES(2,200);
> INSERT INTO t2 VALUES(1,2);
> 
> Later on, I want to change the value of t1(1,100) to (1,101) or insert
> a new row, I don't know which.

Do this:

begin immediate;
update t1 set value = 101 where id = 1;
insert or ignore into t1 (id, value) values (1, 101);
commit;

The update will only happen if the id already exists. The insert will only 
happen if the id doesn't already exist.

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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

Re: [sqlite] INSERT OR UPDATE?

[Jim Morris]

Or do an update and if no records are modified then do an insert.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR UPDATE?

[Simon Slavin]

On 1 Jul 2011, at 7:54pm, KeithB wrote:

> Is there any way to perform the equivalent of an INSERT OR UPDATE statement?
> 
> I have something like this:
> 
> CREATE TABLE t1 (id INTEGER PRIMARY KEY, value INTEGER);
> CREATE TABLE t2 (parent INTEGER REFERENCES t1 ON DELETE CASCADE, child
> INTEGER REFERENCES t1);
> INSERT INTO t1 VALUES(1,100);
> INSERT INTO t1 VALUES(2,200);
> INSERT INTO t2 VALUES(1,2);
> 
> Later on, I want to change the value of t1(1,100) to (1,101) or insert
> a new row, I don't know which.
> 
> I realize I can do INSERT OR REPLACE, but if I have a foreign key
> referencing that table with CASCADE DELETE, the delete propagates and
> t2(1,2) is removed.

You have correctly defined primary keys which makes it easy.

First do an INSERT OR IGNORE … which will IGNORE if the row already exists.
Follow that with an UPDATE.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR UPDATE

[BareFeetWare]

On 10/11/2010, at 7:19 PM, Michele Pradella wrote:

> In-Reply-To:  <4cda28ea.5030...@gmail.com>


Firstly, please start a post to this mail list as a new message, not a reply to 
a previous unrelated message. That's known as "thread hijacking" and confuses 
discussions.

> Hi all, I have to INSERT a row in a DB but I have first to check if the Key 
> I'm inserting already exist.

Good question.

> Now I'm doing a "SELECT count..." first to check if the key exist and then 
> INSERT or UPDATE records.

I can see two problems with this approach:

1. It sounds like you're executing an SQL select, then dropping out of SQL to 
perform a logic test on the result, then executing a new SQL statement 
depending on the logic result. It's more efficient and reliable to do it all in 
SQL.

2. Using "select count" will test the entire table, even if the key exists in 
the first row it tests. You should use "in" or "exists" instead of "count". The 
hit is reduced if the column is indexed, but it's still poor SQL.

> Do you know  if there's a better or faster way to do that?

You could:

begin immediate
;
update MyTable set Detail = newDetail where ID = newID
;
insert into MyTable(ID, Detail) select newID, newDetail where newID not in 
(select ID from MyTable)
;
commit
;

It may be faster to change the insert line to:

insert into MyTable(ID, Detail) select newID, newDetail where changes() > 0 and 
newID not in (select ID from MyTable)

but I don't know, since I haven't really played with changes().

Alternatively, you could use "insert or ignore" or "insert or replace" 
depending on what you want to do if the key exists. Note that "insert or 
replace" will actually delete the old row and insert a new one, something that 
can make a mess if, for instance, you wanted to keep the value of other columns 
or you have internal database logic that is initiated by deletes and inserts, 
so I tend to avoid it.

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

Re: [sqlite] INSERT OR UPDATE

[Simon Slavin]

On 10 Nov 2010, at 8:19am, Michele Pradella wrote:

> Hi all, I have to INSERT a row in a DB but I have first to check if the 
> Key I'm inserting already exist.
> Now I'm doing a "SELECT count..." first to check if the key exist and 
> then INSERT or UPDATE records.
> Do you know  if there's a better or faster way to do that?

> Perhaps with an ON CONFLICT  
> resolution algorithm of INSERT or UPDATE?

You could declare your key as a UNIQUE INDEX.  That would make the INSERT fail 
if it duplicated the key of an existing record.  If you know values for enough 
of the columns you could even declare your key as a UNIQUE INDEX and use an 
'INSERT OR REPLACE'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR UPDATE

[Igor Tandetnik]

Michele Pradella  wrote:
> Hi all, I have to INSERT a row in a DB but I have first to check if the
> Key I'm inserting already exist.
> Now I'm doing a "SELECT count..." first to check if the key exist and
> then INSERT or UPDATE records.
> Do you know  if there's a better or faster way to do that?
> Perhaps with an ON CONFLICT 
> resolution algorithm of INSERT or UPDATE?

See if you can set your table up for INSERT OR REPLACE, as Simon describes. If 
for some reason you cannot, do an UPDATE first, use sqlite3_changes to see if 
any rows were actually affected, and if not do INSERT. This saves you a SELECT.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert or Update (was: ON DELETE CASCADE along with ON CONFLICT REPLACE)

[Pavel Ivanov]

> 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  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

Re: [sqlite] Insert or Update (was: ON DELETE CASCADE along with ON CONFLICT REPLACE)

[Mihai Militaru]

On Tue, 19 Oct 2010 10:54:13 +1100
BareFeetWare  wrote:

> -- 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');

What about using a trigger which automatically updates right before the
insertion? I'm using this trigger:

CREATE TRIGGER users_update_existing
BEFORE INSERT
ON users
BEGIN
UPDATE users
SET type=NEW.type, id=NEW.id
WHERE name=NEW.name;
END

...then in my code I use only:

INSERT OR IGNORE
INTO users(name,type,id)
VALUES(?,?,?);

It works very well so far, I'm just curious if you can forsee any
drawback in this approach.

-- 
Mihai Militaru 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users