Is there a way to insert multiple rows into an SQL table if a WHERE EXISTS check is passed?

Question

I'm trying to insert multiple rows into a table if that table is found to not have any rows matching a WHERE EXISTS check.

Below is my code:

INSERT INTO words (associatedEmotion, word, severity)
SELECT  newWords.associatedEmotion
FROM    ( VALUES ('joy', 'ecstatic', '3' ), ('joy', 'happy', '2') ) AS newWords (associatedEmotion, word, severity)
WHERE   NOT EXISTS ( SELECT 1
                     FROM   words AS MT
                     WHERE  MT.associatedEmotion = newWords.associatedEmotion );

This should check the 'words' table and see if there are any matching results for the set 'associatedEmotion'. However, I am getting an error

Failed to import SQL; message=sqlite3_prepare_v2 failure: near "(": syntax error

This means my syntax is incorrect, but I am unsure why.

forpas · Accepted Answer · 2020-04-29 14:53:04Z

1

Use a CTE like this:

INSERT INTO words (associatedEmotion, word, severity)
WITH newWords(associatedEmotion, word, severity) AS (VALUES ('joy', 'ecstatic', '3' ), ('joy', 'happy', '2'))
SELECT *
FROM  newWords
WHERE  NOT EXISTS ( SELECT 1
                     FROM   words AS MT
                     WHERE  MT.associatedEmotion = newWords.associatedEmotion );

answered Apr 29, 2020 at 14:53

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kickergold Over a year ago

Thanks a lot! Was struggling with that.

Collectives™ on Stack Overflow

Is there a way to insert multiple rows into an SQL table if a WHERE EXISTS check is passed?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related