Yes, very interesting. I didn't realize that the pk would auto-increment on a replace.
-Bill > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Petite Abeille > Sent: Tuesday, May 27, 2014 3:01 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] DELETE & INSERT vs. REPLACE > > > On May 27, 2014, at 8:11 PM, Drago, William @ MWG - NARDAEAST > <william.dr...@l-3com.com> wrote: > > > Is there any difference between using REPLACE as opposed to deleting > records and then inserting new ones to take their place? > > Same difference. > > For example: > > create table foo > ( > id integer primary key not null, > key text not null, > > constraint foo_uk unique( key ) > ); > > sqlite> insert or replace into foo( key ) values( 'a' ); select * from > sqlite> foo; > 1|a > sqlite> insert or replace into foo( key ) values( 'a' ); select * from > sqlite> foo; > 2|a > sqlite> insert or replace into foo( key ) values( 'a' ); select * from > sqlite> foo; > 3|a > > Note how the primary key, id, had changed over time, from 1 to 3. > > For all practical purposes, REPLACE is useless, if not dangerous even. > > What would really be useful would be a MERGE operation instead: > > http://en.wikipedia.org/wiki/Merge_%28SQL%29 > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
