SQL Error in stacked WHERE statements using WHERE NOT EXISTS

Question

I'm having a reoccurring error in my SQL statement, and I cannot figure out how to fix it. The ERROR: syntax error at or near "WHERE" is not very helpful as well.

This is the code snippet in question:

INSERT INTO example.table (first, second, third, fourth, fifth) 
(SELECT first,second,third,fourth,'constant' 
 FROM example.table 
 WHERE some_id=42)
 WHERE NOT EXISTS
 (SELECT 1 
  FROM example.table 
  WHERE (first,third,status)=(SELECT first,third,'request'     FROM example.table 
  WHERE some_id=42));

Everything works as intended up until the 'WHERE NOT EXISTS', but everything also works if I replace the second line with just values instead of searching them from the table. How can I fix this code?

Thanks a lot!

Answer 1

Perhaps

INSERT INTO example.table (first, second, third, fourth, fifth) 
SELECT first,second,third,fourth,'constant' 
FROM example.table 
WHERE some_id=42
 and NOT EXISTS
   (SELECT 1 FROM example.table 
    WHERE (first,third,status)=
       (SELECT first,third,'request' FROM example.table WHERE some_id=42));

Answer 2

There can only be one WHERE clause per DML command, as the syntax error indicates.

But I am also pretty sure you don't want to introduce a 4th (uncorrelated!) instance of example.table in the EXISTS subquery. That would exclude rows based on any row with some_id=42, while you most certainly want to just base that on the row at hand.

So refer back to the instance you are selecting from.

INSERT INTO example.table t1
      (first, second, third, fourth, fifth) 
SELECT first, second, third, fourth, 'constant' 
FROM   example.table t2
WHERE  t2.some_id = 42
AND    NOT EXISTS (
   SELECT FROM example.table t3
   WHERE (t3.first, t3.third, t3.status)
       = (t2.first, t2.third,'request')
   );

Related:

• Select rows which are not present in other table