Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert conditionally in Oracle?

Tags:

sql

oracle

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.

like image 951
Kamil Avatar asked Jan 25 '17 10:01

Kamil


People also ask

How do I write if else condition in Oracle?

Syntax (IF-THEN-ELSIF-ELSE) The syntax for IF-THEN-ELSIF-ELSE in Oracle/PLSQL is: IF condition1 THEN {... statements to execute when condition1 is TRUE...}

What does != Mean in Oracle?

It (<>) is a function that is used to compare values in database table. != (Not equal to) functions the same as the <> (Not equal to) comparison operator.

What are the conditional statements in Oracle?

The condition is a Boolean variable, constant, or expression that evaluates to TRUE, FALSE, or NULL. If condition evaluates to TRUE, the executable statements found after the THEN keyword and before the matching END IF statement are executed. If condition evaluates to FALSE or NULL, those statements are not executed.


2 Answers

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
like image 165
hkutluay Avatar answered Sep 22 '22 22:09

hkutluay


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

like image 40
Kamil Avatar answered Sep 22 '22 22:09

Kamil