"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 <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of
Peng Yu
Sent: Thursday, January 30, 2020 8:21 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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 <david.raym...@tomtom.com> 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 <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 12:02 PM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> 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 <david.raym...@tomtom.com> 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 <sqlite-users-boun...@mailinglists.sqlite.org> On
>> Behalf
>> Of Peng Yu
>> Sent: Thursday, January 30, 2020 11:27 AM
>> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>> 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
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
