CODEWITHSUNDEEP
sqlsqlite
Kickergold
Kickergold

Reputation: 69

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

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.

Upvotes: 0

Views: 36

Answers (1)

forpas
forpas

Reputation: 164089

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 );

Upvotes: 1

Related Questions