I second "Petite Abeille", the merge into (SQL 2003) would be more than useful. REPLACE should be avoided.
On Wed, May 28, 2014 at 5:08 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 28 May 2014, at 3:55pm, Drago, William @ MWG - NARDAEAST > <william.dr...@l-3com.com> wrote: > > > Can someone tell me what an appropriate use for REPLACE is? > > REPLACE in SQlite is just a way of writing > > INSERT OR REPLACE ... > > It's best use is when you are not sure whether a record already exists or > not. And the definition of 'exists' is that the new row has data which > clashes with an existing row by at least one UNIQUE constraint, including > the rule that primary keys must be UNIQUE. > > So an example is if you have a big table of equipment, and a smaller table > listing all equipment which is on loan. One row for each item on loan, and > everything not mentioned in this smaller table should be in the stock room. > The smaller table would have a UNIQUE key on the equipment number to > prevent it from listing one item being on loan to two different people. > > If you discover that item number 515 is on loan to Barry now you need to > make sure that Barry is listed in that table. But you don't know whether > you're creating a new row or replacing a row that said that the equipment > was on loan to Amanda last week. So you use INSERT OR REPLACE and SQLite > works out whether it has to delete an existing row before it can insert the > new one. > > Simon. > _______________________________________________ > 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
