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
