[sqlite] I don't understand how to use NOT EXISTS
On 20 Sep 2015, at 7:15pm, James K. Lowden wrote: > Simon Slavin wrote: > >> Constructions like this >> >>> INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) >>> SELECT 'evil little sister' >> >> should be rewritten so that you are not trying to do a SELECT in the >> middle of your INSERT. > > Why in the world would you say that? That's the SQL assignment > function, the equivalent of > > A = A + B I would argue that that would be UPDATE, not INSERT. But I see your side of the issue. > Anything you'd do to rewrite it would result in something more complex, > doubly so if it included the atomicity guarantee of the original. Suppose the SELECT doesn't return anything. Do you still want to do the INSERT ? Do you now have to look up the values to INSERT elsewhere ? Put the commands in a transaction if that worries you. You're right -- I should probably have written "I preffer" instead if making it an absolute statement. Simon.
[sqlite] I don't understand how to use NOT EXISTS
On Sun, 20 Sep 2015 19:33:35 +0100 Simon Slavin wrote: > On 20 Sep 2015, at 7:15pm, James K. Lowden > wrote: > > > Simon Slavin wrote: > > > >> Constructions like this > >> > >>> INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > >>> SELECT 'evil little sister' > >> > >> should be rewritten so that you are not trying to do a SELECT in > >> the middle of your INSERT. > > > > Why in the world would you say that? That's the SQL assignment > > function, the equivalent of > > > > A = A + B > > I would argue that that would be UPDATE, not INSERT. But I see your > side of the issue. I actually think that's the crux of the matter, Simon. If you think you're operating on a row, then A = A + B is UPDATE, yes. If you think you're operating on tables, then A = A + B is INSERT, and UPDATE is more like A = (A - C) + B where C is the set of rows being replaced by B. > Suppose the SELECT doesn't return anything. Do you still want to do > the INSERT ? If I said WHERE NOT EXISTS, why would I still want to "do the insert"?! I'm with Yoda here: there is no try. > Do you now have to look up the values to INSERT elsewhere ? No, I do not, not if the values I didn't insert can be specified, e.g. insert into S select * from T where exists (select 1 from R where ... ); If the values can't be specified in like manner, that would suggest to me a problem with the database design. TIMTOWTDI, for sure. More than one way to think about it, too. But I see no downside to using INSERT...SELECT, except that it's startling to someone unfamiliar with it. --jkl
[sqlite] I don't understand how to use NOT EXISTS
On Wed, 16 Sep 2015 08:23:04 +1000
Barry Smith wrote:
> As for your original query: think about just the select clause (you
> can run it independently). This will return ('magnetohydrodynamics',
> 1) for each row in the table.
It took me a bit to understand what you meant. I also think there's a
better answer than resorting to LIMIT 1.
To clarify, the OP's query is (reformatted):
> INSERT INTO TAGS ( NAME, COUNT )
> SELECT 'magnetohydrodynamics', 1
> FROM TAGS -- <- the error
> WHERE NOT EXISTS (
> SELECT * FROM TAGS
> WHERE NAME = 'magnetohydrodynamics'
> );
The existence test is against the whole TAGS table. As long as the
name 'magnetohydrodynamics' appears in the table, SELECT will return
zero rows. But -- your point -- if the name tested does *not* appear in
the table, SELECT will return as many rows as are in the table.
The solution is simply to say what's meant instead:
INSERT INTO TAGS ( NAME, COUNT )
SELECT 'magnetohydrodynamics',1
WHERE NOT EXISTS (
SELECT 1 FROM TAGS
WHERE NAME = 'magnetohydrodynamics'
);
As to the OP's question about where he went wrong, the query as
presented should not have created the results he showed. Those results
could be explained, though, if "magnetohydrodynamics" was misspelled in
the WHERE clause. I suspect that's what went wrong.
--jkl
[sqlite] I don't understand how to use NOT EXISTS
On Wed, 16 Sep 2015 19:40:23 +0100 Simon Slavin wrote: > Constructions like this > > > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > > SELECT 'evil little sister' > > should be rewritten so that you are not trying to do a SELECT in the > middle of your INSERT. Why in the world would you say that? That's the SQL assignment function, the equivalent of A = A + B Anything you'd do to rewrite it would result in something more complex, doubly so if it included the atomicity guarantee of the original. --jkl
[sqlite] I don't understand how to use NOT EXISTS
On 2015-09-18 03:13 AM, Keith Medcalf wrote: >> Some initial things. > >> Data needs to be kept in 1NF (or 1st-Normal-Form) which is relational >> theory speak for "Everything without a 1-to-1 relationship with the key >> field in a table, should be in another table". Many reasons for this >> (if you care to read up on some RT) but the most simple reason is: We >> can make complex extractions from 1NF data tables via JOINs, sub-queries >> and the like, but we cannot extract simple information (or de-complicate >> it) from convoluted tables. > Except for very simple cases, data in first normal form that is not also in > third or boyce-codd normal form usually has anomalous update/delete > behaviour. Although it may be complicated, one usually wants to normalize to > at least third normal. Then you can denormalize (usually for performance > reasons) and deal with the anomalies that you (re)introduce through each > denormalization step manually. Indeed, and thank you for adding that. I seem to recall someone on this forum quoting an old adage long ago re this - something to the effect of: "Normalize until it hurts; denormalize until it works." (As long as we avoid Knuth's root of all evil: Premature optimization.) Ryan
[sqlite] I don't understand how to use NOT EXISTS
On 2015-09-17 05:13 AM, Nicolas J?ger wrote: > @Ryan, >> To get you started on this road, may I ask you take a leap of faith >> and explain to us from A to Z what you would like to achieve with the >> tables and data you are asking about above - i.e don't show us the >> programming you think will solve the problem and ask us if that is >> good SQL, rather explain us the problem to be solved and allow us to >> show you the good SQL that will do it. >> >> Every next task should be clearer and easier. > Actualy, it's a personal open source project. So the code could be see > by anyone. The only thing is, the most recent part isn't yet on the > repo (last push was before the summer). But, if for some reason you > want to see the code as it is today on my computer (almost all files > were heavily modified during the summer), I can push it. > > this project is about helping me to manage all files I have on my > computer. I'm a metalhead and science nerd at the same time. > So, I have tons of music and tons of ebooks and publications... So my > project as currently two goals: > >1) store the data on disk, I should not access the files directly. > >2) using a DB to retrieve any document trough a search engine. > > (actually I want to add a third goal: I want to create something I > don't know how I will call it, but it's something like a binder. For > example if you open a binder about 'modelling demons', you can link data > of differents files even if they don't share tags or whatever (can be > in that example some texture, some .blend or pdf about modelling > humanoid...).) > > So, I'm writing some custom GTKmm widgets wich interact with the DB > (but they don't directly access any sqlite function, I'm working with > engines communication (gtkmm_engine, sqlite_engine, etc.)). > > I'm currently thinking to make that program modular. For example, the > user (usualy me), set what kind of data (trough some config file (in > my head I call these files 'collection')) should be store in the > DB. For each data will correspond a widget. > > Let's took an example, > > for ebooks: > > I want to save the filename (filename is ruled by the program, the > user has no direct way to modify it), title, some tags and a description > for each ebook (for the last three the program shows some widgets, > wich allow the user to modify the values). > > At this point, when the program read the ebook.collection, it will > create a first table EBOOK with four columns > (ID,FILENAME,TITLE,DESCRIPTION) > > for the tags, things are more complicated. I need two tables: > - TAGS with three columns (ID,NAME,COUNT) > - TAGSMAP with two columns (ID_EBOOK,ID_TAGS) (I explain the use below) > > so if I take my old example : > > (id|NAME|COUNT) > 53|black metal|3 > 55|occult rock|3 > 61|satanic smurfs|1 > 62|beer|0 > 63|pizza|0 > > if I'm seeking to get all files with the tags 'black metal' the program > check all entries in TAGSMAP with ID_TAGS = 53, > > SELECT * FROM TAGSMAP WHERE ID_TAGS = 53 would returns something like, > > (ID_EBOOK|ID_TAGS) > 3|53 > 5|53 > 9|53 > > then the ID_EBOOKs refer to the IDs of the data (title and description > in this case) for some files in EBOOK. > > now my problems come when you want to add/delete a tags. If for a file > you want (eg) to delete the tag 'black metal' you click on the button I > setup to delete that tag, the program at this time has two > informations: > > the current EBOOK.FILENAME and the TAGS.NAME of the tag. > > So I have to query the DB to get their respective IDs (ID of that > FILENAME in EBOOK and ID of that NAME in TAGS). > > Then, the program has to delete the entry in the TAGSMAP where ID_EBOOK > is the same than ID for the current file in EBOOK and where ID_TAGS > equal the ID in TAGS. > > For TAGS, the program has first to check COUNT. Before the check I have > to get the value from the DB. > >If COUNT = 1, the program has to delete the tag with NAME 'black >metal'. > >If COUNT > 1, the program has to decrement count. > > My first manner to do that, was to just store/read the data to/from the > DB. All conditions (like what I try to do for some days now...) were > executed by the program, not by queries. But the problem (wich is > not really a big issue), I do a lot of queries because of the IFs in my > program then I get one/two seconds of lags (wich may also be here > because I wrote all operation I do in the console for debug > purpose...)... > > So, as I wrote in an earlier email, since I know I can use > transaction, I try to replace all queries in one giant query send to > the DB when I want to update the data for a file (title, tags and > description). > > Maybe, I should ask if what I want to do is really possible by > SQL or if I should better let the IFs in the program better than > struggling like I did in my last emails... > > > I really hope I was clear. If you need further explanation about > something just tell it. Anyway thank you for the time you
[sqlite] I don't understand how to use NOT EXISTS
> Some initial things. > Data needs to be kept in 1NF (or 1st-Normal-Form) which is relational > theory speak for "Everything without a 1-to-1 relationship with the key > field in a table, should be in another table". Many reasons for this > (if you care to read up on some RT) but the most simple reason is: We > can make complex extractions from 1NF data tables via JOINs, sub-queries > and the like, but we cannot extract simple information (or de-complicate > it) from convoluted tables. Except for very simple cases, data in first normal form that is not also in third or boyce-codd normal form usually has anomalous update/delete behaviour. Although it may be complicated, one usually wants to normalize to at least third normal. Then you can denormalize (usually for performance reasons) and deal with the anomalies that you (re)introduce through each denormalization step manually.
[sqlite] I don't understand how to use NOT EXISTS
On 2015-09-16 08:27 PM, Nicolas J?ger wrote: > Hi guys, > > so there is the two ways I made: > > FIRST ONE: > == > > ** Create a table wich will store the result of EXISTS, > > CREATE TABLE _TAG_EXISTS_RESULT_( NAME, BOOL ); > > > ** The next query has to INSERT 'evil little sister' with BOOL to 1 in > _TAG_EXISTS_RESULT_ if 'evil little sister' is already present in TAGS, > > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > SELECT 'evil little sister' > , EXISTS (SELECT 1 FROM TAGS WHERE NAME='evil little sister'); > > > ** Then I add 'evil little sister' if _TAG_EXISTS_RESULT_.BOOL = 0.. > > INSERT INTO TAGS (NAME, COUNT) >VALUES( > CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 >THEN ( SElECT NAME FROM _TAG_EXISTS_RESULT_ ) >ELSE '$NOT_USED$' > END >, 0 ); > > ** delete $NOT_USED$ if so, > > DELETE FROM TAGS WHERE NAME='$NOT_USED$'; > > > ** Then, clear _TAG_EXISTS_RESULT_, > > DELETE FROM _TAG_EXISTS_RESULT_; > > > SECOND ONE: > == > > ** I made a compact version, > > INSERT INTO TAGS (NAME, COUNT) >VALUES >( > CASE( SELECT EXISTS( SELECT 1 FROM TAGS WHERE NAME='evil little > sister' ) ) WHEN 0 > THEN 'evil little sister' > ELSE '$NOT_USED$' > END >, 1 >); > > > ** delete $NOT_USED$ if so, > > DELETE FROM TAGS WHERE NAME='$NOT_USED$'; > > > comments are welcome. It's hard to know where to begin. This looks much more like programming code than SQL - and while there are similarities, the usage is very different. Let me start with explaining the first and most important difference: Most programming languages are imperative (methodical or executive) descriptors - i.e you tell the process/engine/compiler HOW to achieve a task (which seems a bit like what you are trying to do up above). SQL is really a declarative language, i.e. you tell the process/engine WHAT you need, and it will figure out HOW to achieve that in the best and most efficient way. Of course, that is easier said than done, but we've all started with little things and built on from there. Very soon you will see how you can create a schema and some queries to achieve data-handling tasks that used to take you ages to program (at least, that's how it happened for me). To get you started on this road, may I ask you take a leap of faith and explain to us from A to Z what you would like to achieve with the tables and data you are asking about above - i.e don't show us the programming you think will solve the problem and ask us if that is good SQL, rather explain us the problem to be solved and allow us to show you the good SQL that will do it. Every next task should be clearer and easier. Cheers, Ryan
[sqlite] I don't understand how to use NOT EXISTS
Hi, @Simon, > >CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 > > CASE takes a value. You can't put a SQLite command in there. > actually, it's working if you put the command between (). I guess it's like the `` on linux (eg), mplayer `ls *.mp3` but maybe,even if it's working, you want to point that's not correct to use it. > More generally, you come from a programming background and are > expecting SQL to work like a programming language. It doesn't. that's clearly a difficulty for me... > Constructions like this > > > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > > SELECT 'evil little sister' > > should be rewritten so that you are not trying to do a SELECT in the > middle of your INSERT. > > This is why we quoted the form > > INSERT OR IGNORE ...; > UPDATE ...; > > earlier. Two separate commands, not one command trying to run > another. I have to think about that... - @Ryan, > To get you started on this road, may I ask you take a leap of faith > and explain to us from A to Z what you would like to achieve with the > tables and data you are asking about above - i.e don't show us the > programming you think will solve the problem and ask us if that is > good SQL, rather explain us the problem to be solved and allow us to > show you the good SQL that will do it. > > Every next task should be clearer and easier. Actualy, it's a personal open source project. So the code could be see by anyone. The only thing is, the most recent part isn't yet on the repo (last push was before the summer). But, if for some reason you want to see the code as it is today on my computer (almost all files were heavily modified during the summer), I can push it. this project is about helping me to manage all files I have on my computer. I'm a metalhead and science nerd at the same time. So, I have tons of music and tons of ebooks and publications... So my project as currently two goals: 1) store the data on disk, I should not access the files directly. 2) using a DB to retrieve any document trough a search engine. (actually I want to add a third goal: I want to create something I don't know how I will call it, but it's something like a binder. For example if you open a binder about 'modelling demons', you can link data of differents files even if they don't share tags or whatever (can be in that example some texture, some .blend or pdf about modelling humanoid...).) So, I'm writing some custom GTKmm widgets wich interact with the DB (but they don't directly access any sqlite function, I'm working with engines communication (gtkmm_engine, sqlite_engine, etc.)). I'm currently thinking to make that program modular. For example, the user (usualy me), set what kind of data (trough some config file (in my head I call these files 'collection')) should be store in the DB. For each data will correspond a widget. Let's took an example, for ebooks: I want to save the filename (filename is ruled by the program, the user has no direct way to modify it), title, some tags and a description for each ebook (for the last three the program shows some widgets, wich allow the user to modify the values). At this point, when the program read the ebook.collection, it will create a first table EBOOK with four columns (ID,FILENAME,TITLE,DESCRIPTION) for the tags, things are more complicated. I need two tables: - TAGS with three columns (ID,NAME,COUNT) - TAGSMAP with two columns (ID_EBOOK,ID_TAGS) (I explain the use below) so if I take my old example : (id|NAME|COUNT) 53|black metal|3 55|occult rock|3 61|satanic smurfs|1 62|beer|0 63|pizza|0 if I'm seeking to get all files with the tags 'black metal' the program check all entries in TAGSMAP with ID_TAGS = 53, SELECT * FROM TAGSMAP WHERE ID_TAGS = 53 would returns something like, (ID_EBOOK|ID_TAGS) 3|53 5|53 9|53 then the ID_EBOOKs refer to the IDs of the data (title and description in this case) for some files in EBOOK. now my problems come when you want to add/delete a tags. If for a file you want (eg) to delete the tag 'black metal' you click on the button I setup to delete that tag, the program at this time has two informations: the current EBOOK.FILENAME and the TAGS.NAME of the tag. So I have to query the DB to get their respective IDs (ID of that FILENAME in EBOOK and ID of that NAME in TAGS). Then, the program has to delete the entry in the TAGSMAP where ID_EBOOK is the same than ID for the current file in EBOOK and where ID_TAGS equal the ID in TAGS. For TAGS, the program has first to check COUNT. Before the check I have to get the value from the DB. If COUNT = 1, the program has to delete the tag with NAME 'black metal'. If COUNT > 1, the program has to decrement count. My first manner to do that, was to just store/read the data to/from the DB. All conditions (like what I try to do for some days now...) were executed by the program, not by queries. But the problem
[sqlite] I don't understand how to use NOT EXISTS
> @Simon, > > > >CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 > > > > CASE takes a value. You can't put a SQLite command in there. > > > actually, it's working if you put the command between (). I guess it's > like the `` on linux (eg), > > mplayer `ls *.mp3` > > but maybe,even if it's working, you want to point that's not correct to > use it. It is fine to use it that way. A Select inside parenthesis is a "scalar subselect". It will return a single value (in this case BOOL) from the first row returned by the select statement. It is perfectly valid.
[sqlite] I don't understand how to use NOT EXISTS
On 16 Sep 2015, at 7:27pm, Nicolas J?ger wrote: >CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 CASE takes a value. You can't put a SQLite command in there. More generally, you come from a programming background and are expecting SQL to work like a programming language. It doesn't. Constructions like this > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > SELECT 'evil little sister' should be rewritten so that you are not trying to do a SELECT in the middle of your INSERT. This is why we quoted the form INSERT OR IGNORE ...; UPDATE ...; earlier. Two separate commands, not one command trying to run another. Simon.
[sqlite] I don't understand how to use NOT EXISTS
Hi guys, so there is the two ways I made: FIRST ONE: == ** Create a table wich will store the result of EXISTS, CREATE TABLE _TAG_EXISTS_RESULT_( NAME, BOOL ); ** The next query has to INSERT 'evil little sister' with BOOL to 1 in _TAG_EXISTS_RESULT_ if 'evil little sister' is already present in TAGS, INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) SELECT 'evil little sister' , EXISTS (SELECT 1 FROM TAGS WHERE NAME='evil little sister'); ** Then I add 'evil little sister' if _TAG_EXISTS_RESULT_.BOOL = 0.. INSERT INTO TAGS (NAME, COUNT) VALUES( CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 THEN ( SElECT NAME FROM _TAG_EXISTS_RESULT_ ) ELSE '$NOT_USED$' END , 0 ); ** delete $NOT_USED$ if so, DELETE FROM TAGS WHERE NAME='$NOT_USED$'; ** Then, clear _TAG_EXISTS_RESULT_, DELETE FROM _TAG_EXISTS_RESULT_; SECOND ONE: == ** I made a compact version, INSERT INTO TAGS (NAME, COUNT) VALUES ( CASE( SELECT EXISTS( SELECT 1 FROM TAGS WHERE NAME='evil little sister' ) ) WHEN 0 THEN 'evil little sister' ELSE '$NOT_USED$' END , 1 ); ** delete $NOT_USED$ if so, DELETE FROM TAGS WHERE NAME='$NOT_USED$'; comments are welcome. Of course, as I wrote yesterday, if UNIQUE can help to do the job faster, I'll go with it. But this problem show me I do not realy well understand how I have to think when I write a SQL statement. In C you can do something like, IF (condition) THEN (doSomething) END in SQL, it appears to me now, I can't think like this. So I have a question what are the words (like SELECT/UPDATE/INSERT) allowed to start a query ? regards, Nicolas J.
[sqlite] I don't understand how to use NOT EXISTS
If you have a unique index on name, you could use INSERT OR IGNORE.
https://www.sqlite.org/lang_conflict.html
INSERT OR IGNORE INTO TAGS (NAME, COUNT) VALUES ('Bleh', 1)
As for your original query: think about just the select clause (you can run it
independently). This will return ('magnetohydrodynamics', 1) for each row in
the table.
If you did not want to use INSERT OR IGNORE, you could put a LIMIT 1 in there,
or rephrase your query to not use TAGS in the outer select (note: you never
actually reference anything in the TAGS specified in the outer select)
> On 15 Sep 2015, at 1:06 pm, Nicolas J?ger
> wrote:
>
> hi,
> I have a table TAGS with idkey and two colums (NAME, COUNT):
>
> id|NAME|COUNT
> 53|black metal|3
> 55|occult rock|3
> 61|satanic smurfs|1
> 62|beer|0
> 63|pizza|0
>
> I want to check if some tag exist by checking if `NAME` is recorded
> in the table or not. If not, I want to add it;
>
> INSERT INTO TAGS ( NAME, COUNT ) SELECT 'magnetohydrodynamics', 1
> FROM TAGS WHERE NOT EXISTS (SELECT * FROM TAGS WHERE NAME =
> 'magnetohydrodynamics' );
>
> then if I look up in the table I see:
>
> id|NAME|COUNT
> 53|black metal|3
> 55|occult rock|3
> 61|satanic smurfs|1
> 62|beer|0
> 63|pizza|0
> 64|magnetohydrodynamics|1
> 65|magnetohydrodynamics|1
> 66|magnetohydrodynamics|1
> 67|magnetohydrodynamics|1
> 68|magnetohydrodynamics|1
>
> could you tell me please where I did some mess ?
>
> regards,
> Nicolas J.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] I don't understand how to use NOT EXISTS
On 2015-09-15 06:01 PM, Nicolas J?ger wrote:
> hi Keith, hi others,
>
>> If there should only be one entry for each name (and it is not case
>> sensitive),
> I'm using sqlite trough a C++ program wich take care of the case
> sensitive. In this very case, each entry has to be unique.
Fine, but do think of if you ever might want to use the DB via anything
else, or a DB admin program... best is to ensure the DB schema itself
knows the collation and case sensitivity requirements.
>
>> your best bet would be to declare that NAME is unique:
>>
>> create table Tags
>> (
>>id integer primary key,
>>name text collate nocase unique,
>>count integer not null
>> );
>>
>> Then when you want to insert you just do so, as in:
>>
>> INSERT OR IGNORE INTO TAGS (name, count) VALUES
>> ('magnetohydrodynamics', 0);
>>
>> To increment a count you would do:
>>
>> INSERT OR IGNORE INTO TAGS (name, count) VALUES
>> ('magnetohydrodynamics', 0); UPDATE TAGS SET count = count + 1 WHERE
>> name = 'magnetohydrodynamics';
> that's definitely something I want do! thanx!
>
> but I also would like to know how can I check if an entry exists,(or not
> exists), in a table. Like,
>
> IF (exists) THEN (doSomething) END
>
> even if the same entry is present several times I want to execute
> `doSomething` only one time.
That IF..THEN is not SQL so won't ever work. (I know MSSQL allows that
in the TSQL, but that is not an SQL or SQLite thing).
Making the EXISTS work is more easy:
To demonstrate a normal use of EXISTS - this query script is a sort of
merge, it scans a table and then INSERTS the stuff into another table if
it doesn't exist yet, or updates it if it does exist:
-- Make a CTE full of only the items in t1 that already exists in t2...
-- then update column b (in this case) for all of those.
WITH upd(id, a, b, c) AS (
SELECT id, a, b, c FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE
t2.id = t1.id)
)
UPDATE t2 SET t2.b = (SELECT upd.b FROM upd WHERE upd.id=t2.id)
WHERE t2.id IN (SELECT upd.id FROM upd);
-- Make a CTE full of only the items in t1 that does not exist yet
in t2...
-- then add them
WITH ins(id, a, b, c) AS (
SELECT id, a, b, c FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2
WHERE t2.id = t1.id)
)
INSERT INTO t2(id, a, b, c) SELECT id, a, b, c FROM ins;
(Note: We do the update before the insert to simply avoid updating newly
inserted things, but it will work either way round).
Another common use is adding items to a query based on whether or not
they appear in a completely different table, and there is no need to
otherwise JOIN that table to the current query, like this:
-- Say we want to see all the items in t1 that are also found in t2...
SELECT id, a, b, c FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE
t2.id = t1.id);
-- That can of course easily be achieved with a join too, like this:
SELECT id, a, b, c
FROM t1
LEFT JOIN t2 ON t2.id=t1.id
WHERE t2.id IS NOT NULL
It is in fact hard to fathom a simple query where EXISTS is the only
possible solution (i.e. the answer cannot be achieved with a simple
JOIN or WHERE clause addition) which is why Keith said "your best bet
is to..." and then demonstrated a way without using EXISTS.
btw: The whole SELECT 1 WHERE does not have to be 1, you can as
easily select anything in the table, such as id, but since the EXISTS
clause do not care WHAT is selected, it simply sees if ANY rows are
returned, we usually just put a 1 to avoid any wasted cpu cycles.
HTH,
Ryan
[sqlite] I don't understand how to use NOT EXISTS
Hi people!
@Ryan,
> Fine, but do think of if you ever might want to use the DB via
> anything else, or a DB admin program... best is to ensure the DB
> schema itself knows the collation and case sensitivity requirements.
I have to take care of the case sensitivity through the interface of my
program, but I will also consider your comment.
> It is in fact hard to fathom a simple query where EXISTS is the only
> possible solution (i.e. the answer cannot be achieved with a simple
> JOIN or WHERE clause addition) which is why Keith said "your best
> bet is to..." and then demonstrated a way without using EXISTS.
in my present problem, I can use `unique`. I'm not at home know, but I
definitely have to play a little with your solutions.
@Simon,
> you can submit this query and know that there is definitely an answer
> which is definitely a floating point value. And then in your
> programming language you can do your equivalent of
>
> IF (theanswer) > 0.0 THEN (doSomething) END
actualy I do that in C++,
if ( getTagCount( name ) == 0 ) // `0` means not present in the table
{
transaction.Add("INSERT INTO TAGS (NAME, COUNT) VALUES ('"+name+"',
0 );");
}
with `getTagCount()`, a function wich ask the db:
SELECT COUNT FROM TAG WHERE NAME = 'biology';
so if I got nothing from the query, `getTagCount()` returns 0 by itself
otherwise I convert the result given by the query to an integer.
but now, I try to merge all queries in one query since I heard about
`transactions`.
@Igor,
> > sqlite> select count(a) from x;
>
> Better still:
>
> select exists (select 1 from x);
>
> The difference is that the version with count() always scans the
> whole table, while the latter stops at the first record - which is
> good enough if you only need to check for existence, and don't
> actually need the count.
that's a good stuff.
I will think a little, to store the result of some query like,
select exists (select 1 from Tags WHERE name='magnetohydrodynamics');
then using a case over that result...
CASE result WHEN 0 THEN addMe ELSE doNotAddMe END
I have to try, I never used case in Sqlite so far...
thank you guys!
regards,
Nicolas
[sqlite] I don't understand how to use NOT EXISTS
On 15 Sep 2015, at 5:01pm, Nicolas J?ger wrote: > I also would like to know how can I check if an entry exists,(or not > exists), in a table. Like, > > IF (exists) THEN (doSomething) END Here's another alternative to add to those in Ryan's excellent post. With your schema > create table Tags > ( > id integer primary key, > name text collate nocase unique, > count integer not null > ); You can do things like SELECT total(count) FROM Tags WHERE id=234; SELECT total(count) FROM Tags WHERE name='biology'; You will definitely get a reply from this since 'total()' returns 0.0 even if there are no rows which satisfy the WHERE clause. So you can submit this query and know that there is definitely an answer which is definitely a floating point value. And then in your programming language you can do your equivalent of IF (theanswer) > 0.0 THEN (doSomething) END Warning: although SQLite also has the function sum() it does not produce the same result to total() when no lines satisfy the WHERE clause. Sorting out the possibilities is more complicated. Simon.
[sqlite] I don't understand how to use NOT EXISTS
On 9/15/2015 2:04 PM, John McKown wrote: > sqlite> select count(a) from x; Better still: select exists (select 1 from x); The difference is that the version with count() always scans the whole table, while the latter stops at the first record - which is good enough if you only need to check for existence, and don't actually need the count. -- Igor Tandetnik
[sqlite] I don't understand how to use NOT EXISTS
Very good! I'll keep that one. On Tue, Sep 15, 2015 at 1:26 PM, Igor Tandetnik wrote: > On 9/15/2015 2:04 PM, John McKown wrote: > >> sqlite> select count(a) from x; >> > > Better still: > > select exists (select 1 from x); > > The difference is that the version with count() always scans the whole > table, while the latter stops at the first record - which is good enough if > you only need to check for existence, and don't actually need the count. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] I don't understand how to use NOT EXISTS
On Tue, Sep 15, 2015 at 12:43 PM, Simon Slavin wrote: > > On 15 Sep 2015, at 5:01pm, Nicolas J?ger wrote: > > > I also would like to know how can I check if an entry exists,(or not > > exists), in a table. Like, > > > > IF (exists) THEN (doSomething) END > > Here's another alternative to add to those in Ryan's excellent post. With > your schema > > > create table Tags > > ( > > id integer primary key, > > name text collate nocase unique, > > count integer not null > > ); > > You can do things like > > SELECT total(count) FROM Tags WHERE id=234; > SELECT total(count) FROM Tags WHERE name='biology'; > > You will definitely get a reply from this since 'total()' returns 0.0 even > if there are no rows which satisfy the WHERE clause. > Might not work. I'd use count() sqlite> create table x(a int); sqlite> select total(a) from x; 0.0 sqlite> select count(a) from x; 0 sqlite> insert into x values(1); sqlite> insert into x values(-1); sqlite> select count(a) from x; 2 sqlite> select total(a) from x; 0.0 sqlite> > > So you can submit this query and know that there is definitely an answer > which is definitely a floating point value. And then in your programming > language you can do your equivalent of > > IF (theanswer) > 0.0 THEN (doSomething) END > > Warning: although SQLite also has the function sum() it does not produce > the same result to total() when no lines satisfy the WHERE clause. Sorting > out the possibilities is more complicated. > > Simon. > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] I don't understand how to use NOT EXISTS
hi Keith, hi others,
> If there should only be one entry for each name (and it is not case
> sensitive),
I'm using sqlite trough a C++ program wich take care of the case
sensitive. In this very case, each entry has to be unique.
> your best bet would be to declare that NAME is unique:
>
> create table Tags
> (
> id integer primary key,
> name text collate nocase unique,
> count integer not null
> );
>
> Then when you want to insert you just do so, as in:
>
> INSERT OR IGNORE INTO TAGS (name, count) VALUES
> ('magnetohydrodynamics', 0);
>
> To increment a count you would do:
>
> INSERT OR IGNORE INTO TAGS (name, count) VALUES
> ('magnetohydrodynamics', 0); UPDATE TAGS SET count = count + 1 WHERE
> name = 'magnetohydrodynamics';
that's definitely something I want do! thanx!
but I also would like to know how can I check if an entry exists,(or not
exists), in a table. Like,
IF (exists) THEN (doSomething) END
even if the same entry is present several times I want to execute
`doSomething` only one time.
regards,
Nicolas J.
[sqlite] I don't understand how to use NOT EXISTS
hi, I have a table TAGS with idkey and two colums (NAME, COUNT): id|NAME|COUNT 53|black metal|3 55|occult rock|3 61|satanic smurfs|1 62|beer|0 63|pizza|0 I want to check if some tag exist by checking if `NAME` is recorded in the table or not. If not, I want to add it; INSERT INTO TAGS ( NAME, COUNT ) SELECT 'magnetohydrodynamics', 1 FROM TAGS WHERE NOT EXISTS (SELECT * FROM TAGS WHERE NAME = 'magnetohydrodynamics' ); then if I look up in the table I see: id|NAME|COUNT 53|black metal|3 55|occult rock|3 61|satanic smurfs|1 62|beer|0 63|pizza|0 64|magnetohydrodynamics|1 65|magnetohydrodynamics|1 66|magnetohydrodynamics|1 67|magnetohydrodynamics|1 68|magnetohydrodynamics|1 could you tell me please where I did some mess ? regards, Nicolas J.
[sqlite] I don't understand how to use NOT EXISTS
hi, I have a table TAGS with idkey and two colums (NAME, COUNT): id|NAME|COUNT 53|black metal|3 55|occult rock|3 61|satanic smurfs|1 62|beer|0 63|pizza|0 I want to check if some tag exist by checking if `NAME` is recorded in the table or not. If not, I want to add it; INSERT INTO TAGS ( NAME, COUNT ) SELECT 'magnetohydrodynamics', 1 FROM TAGS WHERE NOT EXISTS (SELECT * FROM TAGS WHERE NAME = 'magnetohydrodynamics' ); then if I look up in the table I see: id|NAME|COUNT 53|black metal|3 55|occult rock|3 61|satanic smurfs|1 62|beer|0 63|pizza|0 64|magnetohydrodynamics|1 65|magnetohydrodynamics|1 66|magnetohydrodynamics|1 67|magnetohydrodynamics|1 68|magnetohydrodynamics|1 could you tell me please where I did some mess ? regards, Nicolas J.
[sqlite] I don't understand how to use NOT EXISTS
On Monday, 14 September, 2015 21:07, Nicolas J?ger
said:
> hi,
> I have a table TAGS with idkey and two colums (NAME, COUNT):
> id|NAME|COUNT
> 53|black metal|3
> 55|occult rock|3
> 61|satanic smurfs|1
> 62|beer|0
> 63|pizza|0
> I want to check if some tag exist by checking if `NAME` is recorded
> in the table or not. If not, I want to add it;
> INSERT INTO TAGS ( NAME, COUNT ) SELECT 'magnetohydrodynamics', 1
> FROM TAGS WHERE NOT EXISTS (SELECT * FROM TAGS WHERE NAME =
> 'magnetohydrodynamics' );
The problem is that the query
SELECT 'magnetohydrodynamics', 1
FROM TAGS
WHERE NOT EXISTS (SELECT *
FROM TAGS
WHERE NAME = 'magnetohydrodynamics');
returns a row for each row in tags if there is no 'magnetohydrodynamics' in
tags.
If there should only be one entry for each name (and it is not case sensitive),
your best bet would be to declare that NAME is unique:
create table Tags
(
id integer primary key,
name text collate nocase unique,
count integer not null
);
Then when you want to insert you just do so, as in:
INSERT OR IGNORE INTO TAGS (name, count) VALUES ('magnetohydrodynamics', 0);
To increment a count you would do:
INSERT OR IGNORE INTO TAGS (name, count) VALUES ('magnetohydrodynamics', 0);
UPDATE TAGS SET count = count + 1 WHERE name = 'magnetohydrodynamics';
> then if I look up in the table I see:
>
> id|NAME|COUNT
> 53|black metal|3
> 55|occult rock|3
> 61|satanic smurfs|1
> 62|beer|0
> 63|pizza|0
> 64|magnetohydrodynamics|1
> 65|magnetohydrodynamics|1
> 66|magnetohydrodynamics|1
> 67|magnetohydrodynamics|1
> 68|magnetohydrodynamics|1
>
> could you tell me please where I did some mess ?
>
> regards,
> Nicolas J.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
