conditional insert operations

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

conditional insert operations

Peng Yu
Hi,

Suppose that I have a table with three columns h1, h2, v. I want to
delete all rows with h1=a, and insert rows like the following (data
shown in TSV format), only if there is not an entry with h1=a and
h2="" (empty), it exists but its v is not equal to a value X.

a,A,v1
a,B,v2
...

https://www.sqlite.org/lang_insert.html

I am not sure if there is a way to check an entry to know whether new
entries can be inserted. Could anybody show me an example if this can
be done?

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: conditional insert operations

David Raymond
I'm not quite following what you're trying to do here. Could you provide a few examples of "here's what used to be in there", "here's what I want to insert", "here's what it should like in the end"



-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Thursday, January 30, 2020 11:27 AM
To: SQLite mailing list <[hidden email]>
Subject: [sqlite] conditional insert operations

Hi,

Suppose that I have a table with three columns h1, h2, v. I want to
delete all rows with h1=a, and insert rows like the following (data
shown in TSV format), only if there is not an entry with h1=a and
h2="" (empty), it exists but its v is not equal to a value X.

a,A,v1
a,B,v2
...

https://www.sqlite.org/lang_insert.html

I am not sure if there is a way to check an entry to know whether new
entries can be inserted. Could anybody show me an example if this can
be done?

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: conditional insert operations

Peng Yu
Suppose the table is this (the first line is just header)

h1,h2,v
a,,Y
a,C,3

Since v of h1=a and h2="" is Y which is not X, the table should be updated to

h1,h2,v
a,,X
a,A,1
a,B,2
...


Suppose the table is this, as v of h1=a and h2="" is X, the table is
not changed.

h1,h2,v
a,,X
a,C,3

Suppose the table is this.

h1,h2,v
a,C,3

since there is no row with h1=a and h2="", the table is updated to

h1,h2,v
a,,X
a,A,1
a,B,2
...


On 1/30/20, David Raymond <[hidden email]> wrote:

> I'm not quite following what you're trying to do here. Could you provide a
> few examples of "here's what used to be in there", "here's what I want to
> insert", "here's what it should like in the end"
>
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 11:27 AM
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] conditional insert operations
>
> Hi,
>
> Suppose that I have a table with three columns h1, h2, v. I want to
> delete all rows with h1=a, and insert rows like the following (data
> shown in TSV format), only if there is not an entry with h1=a and
> h2="" (empty), it exists but its v is not equal to a value X.
>
> a,A,v1
> a,B,v2
> ...
>
> https://www.sqlite.org/lang_insert.html
>
> I am not sure if there is a way to check an entry to know whether new
> entries can be inserted. Could anybody show me an example if this can
> be done?
>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: conditional insert operations

David Raymond
Is the combo of h1, h2 unique? If so you could do an upsert (https://www.sqlite.org/lang_UPSERT.html)

create unique index tbl_uidx_h1_h2 on tbl (h1, h2);

insert into tbl values ('a', '', 'X')
on conflict (h1, h2)
do update set v = excluded.v
where v is not excluded.v;


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Thursday, January 30, 2020 12:02 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] conditional insert operations

Suppose the table is this (the first line is just header)

h1,h2,v
a,,Y
a,C,3

Since v of h1=a and h2="" is Y which is not X, the table should be updated to

h1,h2,v
a,,X
a,A,1
a,B,2
...


Suppose the table is this, as v of h1=a and h2="" is X, the table is
not changed.

h1,h2,v
a,,X
a,C,3

Suppose the table is this.

h1,h2,v
a,C,3

since there is no row with h1=a and h2="", the table is updated to

h1,h2,v
a,,X
a,A,1
a,B,2
...


On 1/30/20, David Raymond <[hidden email]> wrote:

> I'm not quite following what you're trying to do here. Could you provide a
> few examples of "here's what used to be in there", "here's what I want to
> insert", "here's what it should like in the end"
>
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 11:27 AM
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] conditional insert operations
>
> Hi,
>
> Suppose that I have a table with three columns h1, h2, v. I want to
> delete all rows with h1=a, and insert rows like the following (data
> shown in TSV format), only if there is not an entry with h1=a and
> h2="" (empty), it exists but its v is not equal to a value X.
>
> a,A,v1
> a,B,v2
> ...
>
> https://www.sqlite.org/lang_insert.html
>
> I am not sure if there is a way to check an entry to know whether new
> entries can be inserted. Could anybody show me an example if this can
> be done?
>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: conditional insert operations

Peng Yu
h1, h2 pair are unique.

I don't quite understand your example. Could you explain what it does
in plain English so that I can be sure it does what I want? (What is
v?)

On 1/30/20, David Raymond <[hidden email]> wrote:

> Is the combo of h1, h2 unique? If so you could do an upsert
> (https://www.sqlite.org/lang_UPSERT.html)
>
> create unique index tbl_uidx_h1_h2 on tbl (h1, h2);
>
> insert into tbl values ('a', '', 'X')
> on conflict (h1, h2)
> do update set v = excluded.v
> where v is not excluded.v;
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 12:02 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] conditional insert operations
>
> Suppose the table is this (the first line is just header)
>
> h1,h2,v
> a,,Y
> a,C,3
>
> Since v of h1=a and h2="" is Y which is not X, the table should be updated
> to
>
> h1,h2,v
> a,,X
> a,A,1
> a,B,2
> ...
>
>
> Suppose the table is this, as v of h1=a and h2="" is X, the table is
> not changed.
>
> h1,h2,v
> a,,X
> a,C,3
>
> Suppose the table is this.
>
> h1,h2,v
> a,C,3
>
> since there is no row with h1=a and h2="", the table is updated to
>
> h1,h2,v
> a,,X
> a,A,1
> a,B,2
> ...
>
>
> On 1/30/20, David Raymond <[hidden email]> wrote:
>> I'm not quite following what you're trying to do here. Could you provide
>> a
>> few examples of "here's what used to be in there", "here's what I want to
>> insert", "here's what it should like in the end"
>>
>>
>>
>> -----Original Message-----
>> From: sqlite-users <[hidden email]> On
>> Behalf
>> Of Peng Yu
>> Sent: Thursday, January 30, 2020 11:27 AM
>> To: SQLite mailing list <[hidden email]>
>> Subject: [sqlite] conditional insert operations
>>
>> Hi,
>>
>> Suppose that I have a table with three columns h1, h2, v. I want to
>> delete all rows with h1=a, and insert rows like the following (data
>> shown in TSV format), only if there is not an entry with h1=a and
>> h2="" (empty), it exists but its v is not equal to a value X.
>>
>> a,A,v1
>> a,B,v2
>> ...
>>
>> https://www.sqlite.org/lang_insert.html
>>
>> I am not sure if there is a way to check an entry to know whether new
>> entries can be inserted. Could anybody show me an example if this can
>> be done?
>>
>> --
>> Regards,
>> Peng
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: conditional insert operations

David Raymond
"What is v?"
That's the name you gave your third field in your example
> Suppose the table is this (the first line is just header)
>
> h1,h2,v


/*
Ensure there is an actual explicitly defined unique constraint on h1, h2
*/
create unique index tbl_uidx_h1_h2 on tbl (h1, h2);


/*
Attempt to insert the new data
*/
insert into tbl (h1, h2, v) values ('a', '', 'X')

/*
If you hit a unique/primary key constraint on (h1, h2)...
*/
on conflict (h1, h2)

/*
then instead of inserting the new record, update the existing record that caused the unique violation,
*/
do update

/*
and update its "v" field to be the value you were trying to insert
*/
set v = excluded.v

/*
But only bother doing this is the new value for "v" is different than the old one.
i.e. Don't bother going through the motions of updating the record if you're not actually going to change any values.
(I used "is not" rather than != so it would work with nulls (or at least, that was my intent))
*/
where v is not excluded.v;



-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Thursday, January 30, 2020 8:21 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] conditional insert operations

h1, h2 pair are unique.

I don't quite understand your example. Could you explain what it does
in plain English so that I can be sure it does what I want? (What is
v?)

On 1/30/20, David Raymond <[hidden email]> wrote:

> Is the combo of h1, h2 unique? If so you could do an upsert
> (https://www.sqlite.org/lang_UPSERT.html)
>
> create unique index tbl_uidx_h1_h2 on tbl (h1, h2);
>
> insert into tbl values ('a', '', 'X')
> on conflict (h1, h2)
> do update set v = excluded.v
> where v is not excluded.v;
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 12:02 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] conditional insert operations
>
> Suppose the table is this (the first line is just header)
>
> h1,h2,v
> a,,Y
> a,C,3
>
> Since v of h1=a and h2="" is Y which is not X, the table should be updated
> to
>
> h1,h2,v
> a,,X
> a,A,1
> a,B,2
> ...
>
>
> Suppose the table is this, as v of h1=a and h2="" is X, the table is
> not changed.
>
> h1,h2,v
> a,,X
> a,C,3
>
> Suppose the table is this.
>
> h1,h2,v
> a,C,3
>
> since there is no row with h1=a and h2="", the table is updated to
>
> h1,h2,v
> a,,X
> a,A,1
> a,B,2
> ...
>
>
> On 1/30/20, David Raymond <[hidden email]> wrote:
>> I'm not quite following what you're trying to do here. Could you provide
>> a
>> few examples of "here's what used to be in there", "here's what I want to
>> insert", "here's what it should like in the end"
>>
>>
>>
>> -----Original Message-----
>> From: sqlite-users <[hidden email]> On
>> Behalf
>> Of Peng Yu
>> Sent: Thursday, January 30, 2020 11:27 AM
>> To: SQLite mailing list <[hidden email]>
>> Subject: [sqlite] conditional insert operations
>>
>> Hi,
>>
>> Suppose that I have a table with three columns h1, h2, v. I want to
>> delete all rows with h1=a, and insert rows like the following (data
>> shown in TSV format), only if there is not an entry with h1=a and
>> h2="" (empty), it exists but its v is not equal to a value X.
>>
>> a,A,v1
>> a,B,v2
>> ...
>>
>> https://www.sqlite.org/lang_insert.html
>>
>> I am not sure if there is a way to check an entry to know whether new
>> entries can be inserted. Could anybody show me an example if this can
>> be done?
>>
>> --
>> Regards,
>> Peng
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users