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

[Nicolas Jäger]

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.