Oracle insert if row does not exist

by

When I run this I get the error “missing INTO keyword” .

Because IGNORE is not a keyword in Oracle. That is MySQL syntax.

What you can do is use MERGE.

merge into table1 t1
    using (select 'value1' as value1 ,value2 
           from table2 
           where table2.type="ok" ) t2
    on ( t1.value1 = t2.value1)
when not matched then
   insert values (t2.value1, t2.value2)
/

From Oracle 10g we can use merge without handling both branches. In 9i we had to use a “dummy” MATCHED branch.

In more ancient versions the only options were either :

  1. test for the row’s existence before issuing an INSERT (or in a sub-query);
  2. to use PL/SQL to execute the INSERT and handle any resultant DUP_VAL_ON_INDEX error.

More Related Contents:

  1. PLSQL Insert into with subquery and returning clause
  2. SQL Query output in VBA is different than in SQL Oracle
  3. I want to find out how much zero come after 1 in SQL
  4. How to use the ‘as’ keyword to alias a table in Oracle?
  5. What is the string concatenation operator in Oracle?
  6. Using an Alias in a WHERE clause
  7. How can I introduce multiple conditions in LIKE operator?
  8. Stored Procedure with optional “WHERE” parameters
  9. subquery in FROM must have an alias
  10. SQL multiple columns in IN clause
  11. How can I insert values into a table, using a subquery with more than one result?
  12. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  13. Selecting the second row of a table using rownum
  14. sqlplus statement from command line
  15. SQL speed up performance of insert?
  16. SQL Server: Cannot insert an explicit value into a timestamp column
  17. Does Oracle store trailing zeroes for Number data type?
  18. Oracle Update Query using Join
  19. What does the colon sign “:” do in a SQL query?
  20. Autoincrement in oracle to already created table
  21. SQL not displaying null values on a not equals query?
  22. What can cause an Oracle ROWID to change?
  23. Oracle query to match all values in the list among all rows in table
  24. Inserting into Oracle and retrieving the generated sequence ID
  25. What does a (+) sign mean in an Oracle SQL WHERE clause? [duplicate]
  26. Connecting to Oracle Database using Sql Server Integration Services
  27. How to bulk insert only new rows in PostreSQL
  28. ORA-00933: SQL command not properly ended
  29. Update a table with data from another table
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment