Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle: how to INSERT if a row doesn't exist

Tags:

oracle

plsql

What is the easiest way to INSERT a row if it doesn't exist, in PL/SQL (oracle)?

I want something like:

IF NOT EXISTS (SELECT * FROM table WHERE name = 'jonny') THEN
  INSERT INTO table VALUES ("jonny", null);
END IF;

But it's not working.

Note: this table has 2 fields, say, name and age. But only name is PK.

like image 310
Topera Avatar asked Oct 01 '10 17:10

Topera


People also ask

How do you insert if row does not exist?

There are three ways you can perform an “insert if not exists” query in MySQL: Using the INSERT IGNORE statement. Using the ON DUPLICATE KEY UPDATE clause. Or using the REPLACE statement.

How do you create a table only if it does not exist in Oracle?

You can do it using PL/SQL Block. Check for table name in user_tables and if table does not exists then create it using Dynamic Query.

How do you check if data exists in a table in Oracle?

Type a short Oracle program, using the following code as a guide: DECLARE record_exists INTEGER; BEGIN SELECT COUNT(*) INTO record_exists FROM your_table WHERE search_field = 'search value' AND ROWNUM = 1; IF record_exists = 1 THEN DBMS_OUTPUT. put_line('Record Exists') ELSE DBMS_OUTPUT.

What does != Mean in Oracle?

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (A != B) is true. > Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.


1 Answers

INSERT INTO table
SELECT 'jonny', NULL
  FROM dual -- Not Oracle? No need for dual, drop that line
 WHERE NOT EXISTS (SELECT NULL -- canonical way, but you can select
                               -- anything as EXISTS only checks existence
                     FROM table
                    WHERE name = 'jonny'
                  )
like image 72
Benoit Avatar answered Sep 16 '22 16:09

Benoit