CODEWITHSUNDEEP
sqlsql-server
Emac
Emac

Reputation: 1167

SQL INSERT INTO if record does not exist (not from a second table)

Through Python, I'm pulling data from a JSON request and inserting into a database, but I want to make sure those records do not already exist before inserting it in there.

I know similar questions have been asked before, but mine is different because mine is not querying from a second table. So how do I approach this?

What I've tried, and I get an incorrect Syntax error, is 

INSERT INTO table (col1, col2, col3) 
VALUES (val1, val2, val3) 
WHERE NOT EXISTS (select col1 FROM table WHERE col1 != val1)

Can anyone give me a better way to approach this problem? Thank you.

Upvotes: 0

Views: 1218

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52147

You can also do something like this:

INSERT INTO table (col1, col2, col3) 
SELECT val1, val2, val3
WHERE NOT EXISTS (SELECT 0 FROM table WHERE col1 = val1)

Note how SELECT is not selecting from a table. It simply returns one row (val1, val2, val3) if the condition holds and zero rows if it doesn't.

BTW, you can write that more succinctly as...

INSERT INTO table (col1, col2, col3) 
SELECT val1, val2, val3
WHERE val1 NOT IN (SELECT col1 FROM table)

...but beware that in the presence of NULLs, this is not the exact equivalent.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

IF NOT EXISTS(SELECT ...)
  INSERT ... VALUES ...

Upvotes: 2

Related Questions