Re: conditional insert operations

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 <[email protected]> On Behalf Of Peng Yu
Sent: Thursday, January 30, 2020 12:02 PM
To: SQLite mailing list <[email protected]>
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 <[email protected]> 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 <[email protected]> On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 11:27 AM
> To: SQLite mailing list <[email protected]>
> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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