CODEWITHSUNDEEP
sqlitediscord.js
Aci
Aci

Reputation: 566

Using WHERE NOT EXISTS in Sqlite returns syntax error

(This relates to a Discord bot using Discord.js and sqlite v3, NOT sqlite3) I'm currently trying to use WHERE NOT EXISTS to add a row to my Sqlite table, but only if there isn't a row where "serverid" is the ID of the current server and the type spam already.

I tried it with this:

sql.run(`INSERT INTO filters WHERE NOT EXISTS(SELECT 1 FROM filters WHERE serverid = "${msg.guild.id}" AND type = "Spam") (serverid, type, active, action, time) VALUES (?, ?, ?, ?, ?)`, msg.guild.id, `Spam`, 0, `delete`, 60)

This works if there ISN'T a row with that ID and type yet, but as soon as it does exist, I get a "syntax error at: WHERE". It doesn't tell me which WHERE the problem is, and I double checked the syntax multiple times and it should be fine. Does this not work in sqlite? Or did I get the syntax wrong?

Upvotes: 0

Views: 602

Answers (1)

forpas
forpas

Reputation: 164099

The syntax of the query is wrong.
You should use INSERT ... SELECT instead of INSERT ... VALUES:

INSERT INTO filters (serverid, type, active, action, time)
SELECT ?, ?, ?, ?, ?
WHERE NOT EXISTS(SELECT 1 FROM filters WHERE serverid = "${msg.guild.id}" AND type = "Spam")`

Upvotes: 2

Related Questions