On Sun, Sep 11, 2011 at 03:00:10PM +0100, Simon Slavin scratched on the wall:
> On 11 Sep 2011, at 2:49pm, Jay A. Kreibich wrote:
>
> >> I think that the 'OR REPLACE' clause refers to the primary key,
> >
> > No, it will trigger on any UNIQUE constraint violation.
> >
> > My guess is that one of the individual columns has a UNIQUE constraint.
> >
> > Is "id" or one of the other columns defined as a PRIMARY KEY?
>
> I agree with Jay. What's happening is this:
>
> New row assembled.
> Attempt to INSERT new row (first attempt)
> INSERT fails UNIQUE contraint.
> Notes that it was an INSERT OR REPLACE, therefore ...
> DELETE original row.
> Attempt to INSERT new row (second attempt)
> INSERT works.
>
> It might go around the loop more than once but it's something like that.
A few important points that are worth calling out:
1) It is INSERT OR REPLACE, not INSERT OR UPDATE. The INSERT always
happens as given... data is never "saved" or "merged" from an
existing row. If there is a UNIQUE constraint conflict, the
conflicting row is simply deleted-- fully and completely.
2) All conflicting rows are deleted. This means a single INSERT OR
REPLACE statement can delete more than one existing rows. Each and
every existing row that would cause a UNIQUE constraint is deleted.
In theory, a row may be deleted for every UNIQUE constraint on the
table.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
