How to insert conditionally in Oracle?

Question

I've read here that the syntax looks like this:

INSERT
WHEN ([Condition]) THEN
INTO [TableName] ([ColumnName])
VALUES ([VALUES])
ELSE
INTO [TableName] ([ColumnName])
VALUES ([VALUES])
SELECT [ColumnName] FROM [TableName];

But I don't want to provide values from another table. I just want to type them, so I've got:

INSERT 
WHEN EXISTS (SELECT 1 FROM FOO WHERE NAME = 'JOE') 
THEN
INTO BAR (NAME, AGE) 
VALUES ('JOE', 50)

and this produces exception: ORA-00928: missing SELECT keyword.

I want to perform an insert if given value is found in another table.

I think you can only use that inside a PL/SQL procedure, not in plain SQL. The `WHEN` clause is not part of the SQL INSERT clause: https://docs.oracle.com/database/121/SQLRF/statements_9014.htm#SQLRF01604 — , Jan 25 '17 at 11:20

score 15 · Accepted Answer · edited Oct 13 '21 at 14:13

15

Using with select works. Your query wasn't working because there is a problem with values keyword when inserting conditionally.

INSERT 
WHEN EXISTS (SELECT 1 FROM FOO WHERE NAME = 'JOE') 
THEN
INTO BAR (NAME, AGE) 
SELECT 'JOE', 50 FROM DUAL

edited Oct 13 '21 at 14:13

Planky

3,185
3
29
39

answered Jan 25 '17 at 11:11

hkutluay

6,794
2
33
53

score 4 · Answer 2 · edited May 23 '17 at 10:29

4

So, I've found an indirect way here and solution for my question would be:

INSERT INTO BAR (NAME, AGE) 
SELECT 'JOE', 50
  FROM DUAL
 WHERE EXISTS (SELECT 1 FROM FOO WHERE NAME = 'JOE')

but it doesn't explain why I have to use SELECT statement in INSERT WHEN

edited May 23 '17 at 10:29

Community

1
1

answered Jan 25 '17 at 11:07

Kamil

1,456
4
32
50

I assume it is because this system relies on the `INSERT` aborting when the `SELECT` it relies on returns no results. – Nyerguds Sep 28 '17 at 12:10

How to insert conditionally in Oracle?

2 Answers2

Linked