Conditional INSERT INTO using SELECT 'WHERE NOT' fails

Question

I have this conditional INSERT INTO using SELECT 'WHERE NOT' that fails. I got the syntax of this by trying to follow examples I found on Google which claim they work. I can't figure out where the syntax is wrong, unless what I am doing is not supported?

INSERT INTO ENV (ENV_ID, ENV_NAME, SSO_URL, ENV_NAME_LOWER)
SELECT '99', 'Blah', 'https://blah.com:443', 'production'
WHERE NOT EXISTS (SELECT 1 FROM ENV WHERE ENV_ID = '99');

And here is the error I get in Oracle:

Error at Command Line : 3 Column : 1
Error report -
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

UPDATE: Got a satisfactory answer but couldn't completely get it working due to another error: ORA-54013: INSERT operation disallowed on virtual columns

Ok, so you are implying that the only way to do this might be using a MERGE statement? — djangofan, Dec 21 '15 at 23:05
@RichardHamilton OP's syntax is correct in this regard, you may feed an insert statement with a query result in sql. — collapsar, Dec 21 '15 at 23:08
The problem is `select ... where...` without the `from`. Either tweak your sql statement or use `from dual`... — sgeddes, Dec 21 '15 at 23:12

score 4 · Accepted Answer · answered Dec 21 '15 at 23:23

4

The syntax of the outer query is incorrect due to a missing from clause. That needs to be specified notwithstanding that you are using literal values only. Oracle provides the 'dual' table for this purpose:

INSERT INTO ENV (ENV_ID, ENV_NAME, SSO_URL, ENV_NAME_LOWER)
     SELECT '99', 'Blah', 'https://blah.com:443', 'production'
       FROM DUAL
      WHERE NOT EXISTS (SELECT 1 FROM ENV WHERE ENV_ID = '99')
          ;

The statement will fail iff the env record tested for exists.

answered Dec 21 '15 at 23:23

collapsar

17,010
4
35
61

Thanks, that looks like the syntax is correct. I think I am limited by my database permissions because I get this error: ORA-54013: INSERT operation disallowed on virtual columns – djangofan Dec 22 '15 at 00:22
Well, maybe, some of the columns you referenced are, you know, [virtual](https://oracle-base.com/articles/11g/virtual-columns-11gr1)? Use this query: `select column_name, virtual_column from user_tab_cols where table_name = 'ENV'`. Virtual columns will have 'YES'. – Paul Dec 22 '15 at 17:05

Conditional INSERT INTO using SELECT 'WHERE NOT' fails

1 Answers1