On Monday, 14 September, 2015 21:07, Nicolas J?ger <jagernicolas at legtux.org>
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
