On Fri, Mar 09, 2012 at 09:49:22AM +1100, BareFeetWare wrote:
> I suggest only using "insert or replace" if you genuinely want to delete and
> replace with a new row. This is generally not hat you want if there are any
> foreign keys.
>
> Only use "insert or ignore" if you are inserting exactly the same row as what
> might already be there.
>
> Otherwise use a combination of insert and update.
>
> So, for example, if you are wanting to add a person that may not already be
> in your table:
>
> insert or ignore into "Person" ("First Name", "Last Name", "Company", "Email")
> select 'Tom', 'Brodhurst-Hill', 'BareFeetWare', 'develo...@barefeetware.com')
> ;
> update "Person"
> set "Email" = 'develo...@barefeetware.com'
> where "First Name" = 'Tom' and "Last Name" = 'Brodhurst-Hill'
> ;
I've had a similar problem in the past, and solved it by using a pre-insert
trigger to do the desired update.
Thus, the insert/update from above becomes just an insert (or ignore),
with an implied update in the trigger, which appears to be sematically
closer to what people want in the above case (though not in the original
subject matter.)
In my case, it was inserting "event" data if not already existing, else
updating an existing event record from the new event data (such as incrementing
an event count and updating timestamps):
create table events (
identifier text primary key,
count integer default 1,
firstoccurrence date not null,
lastoccurrence date not null,
<other fields>....
);
create trigger event_dedup
before insert on events
for each row
begin
update events set count=count+1,
lastoccurrence=NEW.lastoccurrence where identifier=NEW.identifier;
end;
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
1',datetime('now'),datetime('now'));
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
2',datetime('now'),datetime('now'));
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
1',datetime('now'),datetime('now'));
<pause>
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
1',datetime('now'),datetime('now'));
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
2',datetime('now'),datetime('now'));
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
1',datetime('now'),datetime('now'));
select * from events;
Some event 1|4|2012-03-12 13:34:43|2012-03-12 13:34:48|...
Some event 2|2|2012-03-12 13:34:43|2012-03-12 13:34:48|...
It must be noted as well that the above "select or ignore" data can be
generated from a select, so in the original question, the "update" could
be written as:
insert or ignore into t1 ( tid, a, b ) select t1.tid, t1.a+t2.a,
t1.b+t2.b from t1 join t2 on (t1.tid=t2.tid);
So long as the update in the pre-insert trigger updates all the required
fields. The select with join is only evaluated once, so will be more
efficient if the join was the dominant performance bottleneck, and emulates
the "update from select" noted in oracle elsewhere in the thread.
Christian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
