[sqlite] I don't understand how to use NOT EXISTS

[R.Smith]

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

[Nicolas Jäger]

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

[Simon Slavin]

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] sqlite3 file as database

[Simon Slavin]

On 15 Sep 2015, at 1:16pm, John McKown  wrote:

> Like the "resource fork" on the older MacOS systems? I think that OS/2 also
> has "extended attributes"(?) which could be set.
> https://en.wikipedia.org/wiki/Extended_file_attributes

Resource forks were intended for content rather than meta-data.  But the Mac 
always had the Creator/Type system that was mentioned earlier in the thread.  
They were each four characters long, so you'd have a Creator/type of

ttxt/text = a text file created by 'TeachText' (equivalent of NOTEPAD)
mpnt/jpeg = an image file in JPEG format created by MacPaint
CDrw/jpeg = an image file in JPEG format created by Corel Draw

etc..

The creator told the operating system which application should be launched by 
default to open the file.  The type told all applications what they'd find if 
they opened the file, and using that they could decide whether they should be 
opening the file at all.

The filename was completely ignored by the original MacOS.  It paid attention 
only to those settings.  They weren't part of the filename and you needed to 
run a little utility to change them, which meant that the sort of users who 
would mess things up by changing a file extension were not able to mess them up.

It was an excellent system, far better than just having a three character file 
extension.

Simon.

[sqlite] I don't understand how to use NOT EXISTS

[Igor Tandetnik]

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] New Delphi SQLite data access components

[Denis Yanov]

Devart company released new SQLite Delphi data access components named LiteDAC 
2.6. 

List of improvements and fixes:

- RAD Studio 10 Seattle is supported
- INSERT, UPDATE and DELETE batch operations are supported
- Now Trial for Win64 is a fully functional Professional Edition
- Now at automatic refresh of Detail dataset the OnBeforeOpen event is not 
called
- Now the Direct mode is based on the SQLite engine version 3.8.11.1
- The EnableSharedCache option of the Connection component for non-Windows 
platforms is added
- Generating conditions for filtration when using similar field names and 
aliases in TCRDBGrid is fixed
- SQLMonitor behavior on using similar objects is fixed
- Bug with endless reconnection in assigned connection is fixed
- Bug with editing Blob and Memo fields used in local filter is fixed
- Bug with a null CURRENT_TIME value on MacOS, iOS & Android in Direct Mode is 
fixed
- Bug with handling DATETIME fields with milliseconds is fixed
- Bug with attaching a database containing international characters in the 
encryption key is fixed
- Bug with database encryption in Direct Mode on MacOS is fixed
- Bug with the "Unable to open database" error when using a temp database on 
Android is fixed
- Bug with CRBatchMove component when using complex table names is fixed
- Bug when calling SQL for trigger creation is fixed
- Bug with detecting AutoInc fields on mobile platforms is fixed

More info Download LiteDAC 2.6

[sqlite] New SQLite dbExpress driver

[Denis Yanov]

Devart team is proud to introduce the new version of Devart dbExpress driver 
for SQLite including such significant features as:

- RAD Studio 10 Seattle is supported and now you can use the latest IDE to 
develop cross-platform applications.

- Now the trial limitation by 6 columns is removed from the Trial version for 
Win64 and it becomes a fully-functional Professional Edition.

More info  Download the driver

[sqlite] I don't understand how to use NOT EXISTS

[John McKown]

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

[John McKown]

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

[Nicolas Jäger]

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] sqlite3 file as database

[John McKown]

Like the "resource fork" on the older MacOS systems? I think that OS/2 also
has "extended attributes"(?) which could be set.
https://en.wikipedia.org/wiki/Extended_file_attributes
>

In OS/2  version 1.2 and later, the High
Performance File System
 was designed
with extended attributes in mind, but support for them was also
retro-fitted on the FAT
 filesystem of DOS.
For compatibility with other operating systems using a FAT partition, OS/2
attributes are stored inside a single file "EA DATA. SF" located in the
root directory. This file is normally inaccessible when an operating system
supporting extended attributes manages the disk, but can be freely
manipulated under, for example, DOS. Files and directories having extended
attributes use one or more clusters
 inside this file. The
logical cluster number of the first used cluster is stored inside the
owning file's or directory's directory entry
. These two
bytes are used for other purposes on the FAT32 filesystem, and hence OS/2
extended attributes cannot be stored on this filesystem.

Parts of OS/2 version 2.0 and later such as the Workplace Shell
 uses several standardized
extended attributes (also called *EAs*) for purposes like identifying the
filetype, comments, computer icons
 and keywords about the file.
Programs written in the interpreted language Rexx
 store an already parsed
 version of the code as an extended
attribute, to allow faster execution.


On Mon, Sep 14, 2015 at 2:02 PM, Tim Streater  wrote:

> On 14 Sep 2015 at 19:29, Warren Young  wrote:
>
> > On Sep 14, 2015, at 8:38 AM, Stephen Chrzanowski 
> wrote:
> >>
> >> There are many extensions of the same .. err..
> >> name(?)...value(?)..structure(?) that are completely different things.
> >
> > It?s fairly bad in the electronics engineering world, where it seems like
> > half the tools use *.sch for schematics and *.brd/pcb for printed circuit
> > board layouts, but none of the tools agree on the format of the actual
> file
> > data.  If you have two such apps installed, you have to make a hard
> choice
> > about which app becomes the default to open such files, and occasionally
> have
> > to fix it when updating the other app, as it takes over the extensions
> again.
> >
> > This widespread unwillingness to get beyond the 8.3 limits, particularly
> on
> > Windows, is annoying.  We haven?t had to worry about compatibility with
> > 3-character file extensions since Windows NT 3.5 and Windows 95, two
> decades
> > ago now.
>
> Of course in a sensible world, OS providers would all have implemented a
> common metadata API, and no one would need or use extensions.
>
> --
> Cheers  --  Tim
>
> ___
> 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] sqlite3 file as database

[Jean-Christophe Deschamps]

At 01:07 15/09/2015, you wrote:
 >---
>SQUISH was a database format for storing messages in FidoNet systems.
 >---

Geez, I don't even recall my FidoNet node number aka address... Time 
must have flown by faster than I thought.

--
jcd 

[sqlite] UNIQUE constraint failed: ip_addr.pid

[R.Smith]

On 2015-09-14 09:04 PM, Petr L?z?ovsk? wrote:
> Have following table:
>
> CREATE TABLE ip_addr
> (
> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL,
> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL,
> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */
> /*! Status: 1 - Allowed, Asigned to concrete customer */
> /*! Status: 2 - Disallowed, Assigned to blocked user */
> /*! Status: 3 - Disallowed, Assigned to history user */
> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL,
> /*! Type: 1 - Private */
> /*! Type: 2 - Public */
> /*! Type: 3 - IPv6 */
> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL,
> /*! Date of blocking %%e */ blocked_at INTEGER,
> /*! Blocking note %%f */ blocking_note VARCHAR
> );
> )
>
>
> If inserting row containing PID already exist in table, sqlite generate 
> %subj% error. But there is not such constraint in database definition. Did I 
> miss something?

Hi Lazno,

I'm guessing you know by now about the uniqueness of a PK, but just in 
case, here is the link to get the short version direct meaning:
https://www.google.com/search?btnG=1=0=Explain+primary+key%3F_rd=ssl

Next step, let me suggest how to solve your problem, this is the schema 
you might need:

CREATE TABLE ip_addr (
   /*  Primary identificator (ID) %%a  */  id   INTEGER PRIMARY KEY,
   /*  Person identificator (PID) %%a  */  pid  INTEGER NOT NULL,
   /*  IP address %%b  */  ip_addr  VARCHAR(16) NOT NULL,
   /*  Status: 0 - Allowed, Unassigned to specific customer (blocked) */
   /*  Status: 1 - Allowed, Asigned to concrete customer  */
   /*  Status: 2 - Disallowed, Assigned to blocked user   */
   /*  Status: 3 - Disallowed, Assigned to history user   */

   /*  Status of IP address %%c   */   ip_status  INTEGER   NOT NULL,
   /*  Type: 1 - Private  */
   /*  Type: 2 - Public   */
   /*  Type: 3 - IPv6 */
   /*  Type of IP address %%d  */  ip_typeINTEGER   NOT NULL,
   /*  Date of blocking %%e*/  blocked_at INTEGER,
   /*  Blocking note %%f   */  blocking_note TEXT
);

CREATE INDEX pid_idx ON ip_addr(pid);  /*  Non-Unique Index for pid
allowing Indexing with multiple of the same ip's  */




Notes:
When you declare any column as INTEGER PRIMARY KEY, it becomes an alias 
for the row_id, which means that:
- A: It must be Unique,
- B: It cannot contain NULLs by design (Other primary keys may contain 
NULLs, but not the row_id), so no need to add NOT NULL, and
- C: If you do not intend to access the primary key ever (say you will 
only ever look at the pid Index here), then you can just leave out this 
line:

   /*  Primary identificator (ID) %%a  */  id   INTEGER PRIMARY KEY,


Also, note that SQLite can do VARCHAR(16) and while the 16 is useful 
(for query optimization for instance), the column won't ever be 
constrained to 16 chars, you can easily put 200 chars in there and it 
will keep the full line.

Lastly, in the last line where VARCHAR appeared without a range 
specifier, you could use just TEXT (as in my example) which is the 
native SQLite type (or affinity) for string-type data.

If you will use look-ups by any of the text fields, I suggest Indexing 
them and also add COLLATE NOCASE in the declaration to avoid case 
sensitivity.

Cheers,
Ryan

[sqlite] UNIQUE constraint failed: ip_addr.pid

[Petr Lázňovský]

Thay track all of us and "optimalise" search results. My list of links could 
differ from yours.

I have googled key phrase '"UNIQUE constraint failed:" sqlite primary key' and 
similar, but not read whole received documents, perform only brief look on it. 
This is best practice by my experience, english is not my first language and 
reading whole long documents is painfullness for me.

Apology to boring experts with dumb question.

L.

> Hi Petr,

> if you Google for "database table primary key" the first few results lead to 
> quite good explanations.
> Also, the english wikipedia's article "Unique key" explains primary keys.

> HTH
> Martin


> Am 14.09.2015 22:25 schrieb Petr L?z?ovsk? :

>> I had googled to verify such idea before, but have no luck.

>> Thanks, L.

>> > Hello Petr,

>> > defining the column pid as INTEGER PRIMARY KEY you added an implicit 
>> > contraint; a primary key means that only one record with a given value 
>> > of pid can exist in the table.
>> > See https://www.sqlite.org/lang_createtable.html#rowid

>> > Martin

>> > Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?:
>> >> Have following table:

>> >> CREATE TABLE ip_addr
>> >> (
>> >> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL,
>> >> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL,
>> >> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */
>> >> /*! Status: 1 - Allowed, Asigned to concrete customer */
>> >> /*! Status: 2 - Disallowed, Assigned to blocked user */
>> >> /*! Status: 3 - Disallowed, Assigned to history user */
>> >> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL,
>> >> /*! Type: 1 - Private */
>> >> /*! Type: 2 - Public */
>> >> /*! Type: 3 - IPv6 */
>> >> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL,
>> >> /*! Date of blocking %%e */ blocked_at INTEGER,
>> >> /*! Blocking note %%f */ blocking_note VARCHAR
>> >> );
>> >> )

>> >> If inserting row containing PID already exist in table, sqlite generate 
>> >> %subj% error. But there is not such constraint in database definition. 
>> >> Did I miss something?

>> >> L.

>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users at mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>> ___
>> 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

[Nicolas Jäger]

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

[Nicolas Jäger]

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.