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
