INSERT WITH NOT EXISITS

Question

I'd like to combine an insert query with a "where not exists"

Here my SQL :

INSERT INTO MYTABLE (ACT_ORDER, MNU_ACTION, ACT_STATUT, ACT_BEFORE, ACT_AFTER)
SELECT (1,'acOuvPOS', 'T', 'T', NULL)
WHERE NOT EXISTS (SELECT * FROM MYTABLE WHERE  (MNU_ACTION ='acOuvPOS' AND ACT_STATUT = 'T' AND ACT_BEFORE= 'T');

But when I execute the SQL I have this problem

Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 2, char 8. ,.

How can I resolve this please Thanks in advance

Sorry I miss the closing ) when coping the requet, I use Interbase 6.0.2 — mimou_2009, Oct 21 '16 at 14:29
NO Sorry , I have this error , Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 2, char 6. FROM. — mimou_2009, Oct 21 '16 at 14:47
Errors aside, the `NOT EXISTS` clause will always be the same, regardless of which record is being selected. You might want to rethink your logic. — Tim Biegeleisen, Oct 21 '16 at 14:53

score 0 · Answer 1 · answered Oct 22 '16 at 09:57

0

INSERT INTO MYTABLE (ACT_ORDER, MNU_ACTION, ACT_STATUT, ACT_BEFORE, ACT_AFTER) SELECT 1,'acOuvPOS', 'T', 'T', NULL WHERE NOT EXISTS (SELECT * FROM MYTABLE WHERE MNU_ACTION ='acOuvPOS' AND ACT_STATUT = 'T' AND ACT_BEFORE= 'T')

answered Oct 22 '16 at 09:57

Mansoor

4,061
1
17
27

Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 1, char 0. IF. – mimou_2009 Oct 24 '16 at 11:43
Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 2, char 36. WHERE. – mimou_2009 Oct 24 '16 at 12:16

score 0 · Answer 2 · answered Oct 22 '16 at 10:43

you can simply say :

IF ( NOT EXISTS (SELECT * FROM MYTABLE WHERE (MNU_ACTION ='acOuvPOS' AND ACT_STATUT = 'T' AND ACT_BEFORE= 'T') BEGIN INSERT INTO MYTABLE (ACT_ORDER, MNU_ACTION, ACT_STATUT, ACT_BEFORE, ACT_AFTER) (1,'acOuvPOS', 'T', 'T', NULL) END

INSERT WITH NOT EXISITS

2 Answers2