CODEWITHSUNDEEP
sqloracle-database
Shaan
Shaan

Reputation: 598

Oracle - Conditional Insert with Sequence

I know the sequence can not be used in these places.

For a SELECT statement:

  1. In a WHERE clause
  2. In a GROUP BY or ORDER BY clause
  3. In a DISTINCT clause
  4. Along with a UNION or INTERSECT or MINUS

In a sub-query

Please help to achieve the below requirement with conditional insert. Objective is - Need to insert if the name is not exist in the table as its protected by Sequence as primary key.

INSERT 
WHEN EXISTS (SELECT 0 FROM TABLE1 WHERE NAME = 'DUPLICATE_NAME_TEST') 
THEN
INTO TABLE1 (KEY, NAME, GROUP) 
SELECT TESTSEQ.NEXTVAL, 'DUPLICATE_NAME_TEST', 30 FROM DUAL;

Upvotes: 0

Views: 375

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

SQL has no INSERT WHEN construct. It does have WHERE. So you intend something like this:

INSERT INTO TABLE1 (NAME, GROUP)
    SELECT 'DUPLICATE_NAME_TEST', 30
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1 FROM TABLE1 T1 WHERE T1.NAME = 'DUPLICATE_NAME_TEST');

Upvotes: 1

Related Questions