Apologies, it seems you got lots of replies explaining why, but I failed to mention how it would work.
Try this in your code: CREATE TABLE messages (id numeric PRIMARY KEY); insert or replace into messages values (1); insert or replace into messages values (1); insert or replace into messages values (1); Another few possibilities to achieve uniqueness similar to the above are: CREATE TABLE messages ( id numeric, PRIMARY KEY (id) -- Sets the id as the Primary key ); CREATE TABLE messages ( id numeric, CONSTRAINT "UIdxMessages" UNIQUE (id) -- Creates a Unique index on id ); CREATE TABLE messages ( id numeric, ); CREATE UNIQUE INDEX UIdxMEssages ON messages(id); -- Same as above but outside of Table dec On 2016/03/09 5:10 PM, R Smith wrote: > > > On 2016/03/09 5:03 PM, Tilsley, Jerry M. wrote: >> All, >> >> I'm trying to use the INSERT OR REPLACE syntax so that I don't have >> to worry about duplicate entries without creating a trigger. Using >> the schema: >> CREATE TABLE messages (id numeric); >> >> and using the SQL: >> insert or replace into messages values (1); >> >> Why does this result in multiple rows of the same value? I'm I just >> not understanding the syntax correctly? > > Nothing wrong with the syntax. I assume you don't have a unique > constraint (Index) on that column, so the DB has no trouble adding > more than one entry. > > The REPLACE mechanism only works if it Can't insert a row because one > already exists AND the DB does not allow more than one to exist, only > then does it replace the previous. > > Hope that was the problem! > Ryan > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
