Re: [sqlite] DELETE & INSERT vs. REPLACE

[Nico Williams]

On May 29, 2014 4:59 PM, "J Decker"  wrote:
> If you have constraints between your tables (like cascade on delete) then
> deleting a guitar entry would remove its related entirs in other tables
> referencing that same ID.

That's what deferred foreign key constraints are for, iiuc.  But on
delete/insert triggers will still run if the replace becomes a delete and
an insert.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE & INSERT vs. REPLACE

[J Decker]

>
>
> Can someone tell me what an appropriate use for REPLACE is?
>
>
Replace might be used if you had a user_permission_info table, and were
editing things about the user like maybe lastname/staffID ... password...
but then with replace you MUST reference all unique keys...but basicallly
the user is still the same user; not a new guitar

If you have constraints between your tables (like cascade on delete) then
deleting a guitar entry would remove its related entirs in other tables
referencing that same ID.

But ya I agree, generally it is a more robust solution to use if
'update' fails, then insert;

-Bill
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Nico Williams]

I tend to use an UPDATE ... WHERE EXISTS ...; followed by an INSERT ...
WHERE NOT EXISTS ...;, which gives me insert or update semantics.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Sylvain Pointeau]

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  wrote:

>
> On 28 May 2014, at 3:55pm, Drago, William @ MWG - NARDAEAST
>  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

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Simon Slavin]

On 28 May 2014, at 3:55pm, Drago, William @ MWG - NARDAEAST 
 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

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Tuesday, May 27, 2014 5:21 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] DELETE & INSERT vs. REPLACE
>
>
> On 27 May 2014, at 9:12pm, Drago, William @ MWG - NARDAEAST
> <william.dr...@l-3com.com> wrote:
>
> > Let me rephrase the question by asking, how should I overwrite
> existing data? For example, Jane has 5 guitars in her instrument table.
> She trades her 5 guitars for 5 new ones. Almost everything about the
> new guitars is different from the old guitars.
> >
> > My gut tells me I should simply delete where instrument_type =
> 'guitar' and then insert the new data.
>
> Go with your gut.  Since there's no relationship between the old and
> new guitars it would be just as likely that Jane would replace 5
> guitars with 4 guitars, having sold two cheap ones and bought one
> expensive one.  You should not be using REPLACE, you should be using
> DELETE, then INSERT, with the correct number of each type of command.
>
> Of course, unless your entire databases is about Jane's guitars that's
> not a good table to create.  You would be more likely to have a
> database about everything Jane owns, or a database about all the
> guitars lots of people owns.

Thank you. That's good news and I don't have to change any of my code.

Can someone tell me what an appropriate use for REPLACE is?

-Bill
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

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Simon Slavin]

On 27 May 2014, at 9:12pm, Drago, William @ MWG - NARDAEAST 
 wrote:

> Let me rephrase the question by asking, how should I overwrite existing data? 
> For example, Jane has 5 guitars in her instrument table. She trades her 5 
> guitars for 5 new ones. Almost everything about the new guitars is different 
> from the old guitars.
> 
> My gut tells me I should simply delete where instrument_type = 'guitar' and 
> then insert the new data.

Go with your gut.  Since there's no relationship between the old and new 
guitars it would be just as likely that Jane would replace 5 guitars with 4 
guitars, having sold two cheap ones and bought one expensive one.  You should 
not be using REPLACE, you should be using DELETE, then INSERT, with the correct 
number of each type of command.

Of course, unless your entire databases is about Jane's guitars that's not a 
good table to create.  You would be more likely to have a database about 
everything Jane owns, or a database about all the guitars lots of people owns.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Drago, William @ MWG - NARDAEAST]

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

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, May 27, 2014 2:15 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] DELETE & INSERT vs. REPLACE
>
> On Tue, May 27, 2014 at 2:11 PM, Drago, William @ MWG - NARDAEAST <
> william.dr...@l-3com.com> wrote:
>
> > All,
> >
> > Is there any difference between using REPLACE as opposed to deleting
> > records and then inserting new ones to take their place?
> >
>
> REPLACE only deletes records when it is absolutely necessary to
> preserve UNIQUE constraints on an insert.  As long as you restrict your
> deletes to records that would otherwise cause a uniqueness conflict,
> then the result will be the same.


There are no uniqueness constraints on this table.

Let me rephrase the question by asking, how should I overwrite existing data? 
For example, Jane has 5 guitars in her instrument table. She trades her 5 
guitars for 5 new ones. Almost everything about the new guitars is different 
from the old guitars.

My gut tells me I should simply delete where instrument_type = 'guitar' and 
then insert the new data. If there's a better way (i.e. healthier for the 
database) then I'd like to know. In my real world automatic test application 
things like will happen very infrequently, but they will occasionally happen. 
And, if it matters, this is a very low volume database. No more than a dozen or 
so inserts/reads per day.

I'd like to keep the database healthy by not doing inefficient things to it and 
I'm new to both SQLite and SQL. That's why I'm asking this question.

Thanks,
-Bill

> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Petite Abeille]

On May 27, 2014, at 8:11 PM, Drago, William @ MWG - NARDAEAST 
 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' );
sqlite> select * from foo;
1|a
sqlite> insert or replace into foo( key ) values( 'a' );
sqlite> select * from foo;
2|a
sqlite> insert or replace into foo( key ) values( 'a' );
sqlite> select * from 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

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Richard Hipp]

On Tue, May 27, 2014 at 2:11 PM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:

> All,
>
> Is there any difference between using REPLACE as opposed to deleting
> records and then inserting new ones to take their place?
>

REPLACE only deletes records when it is absolutely necessary to preserve
UNIQUE constraints on an insert.  As long as you restrict your deletes to
records that would otherwise cause a uniqueness conflict, then the result
will be the same.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DELETE & INSERT vs. REPLACE

[Drago, William @ MWG - NARDAEAST]

All,

Is there any difference between using REPLACE as opposed to deleting records 
and then inserting new ones to take their place?

Thanks,
--
Bill Drago
Senior Engineer

L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

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