Oracle: how to UPSERT (update or insert into a table?)

by

The MERGE statement merges data between two tables. Using DUAL
allows us to use this command. Note that this is not protected against concurrent access.

create or replace
procedure ups(xa number)
as
begin
    merge into mergetest m using dual on (a = xa)
         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;

A                      B
---------------------- ----------------------
10                     2
20                     1

More Related Contents:

  1. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  2. Receive an unexpected symbol error in my PLSQL query [closed]
  3. SQL Query to concatenate column values from multiple rows in Oracle
  4. Search All Fields In All Tables For A Specific Value (Oracle)
  5. Select First Row of Every Group in sql [duplicate]
  6. Return row with the max value of one column per group [duplicate]
  7. How do I ignore ampersands in a SQL script running from SQL Plus?
  8. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  9. How can I merge two MySQL tables?
  10. Oracle SQL, concatenate multiple columns + add text
  11. Oracle query to fetch column names
  12. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  13. SQL Error: ORA-01861: literal does not match format string 01861
  14. Determine Oracle null == null
  15. How to anticipate and escape single quote ‘ in oracle
  16. INSERT SELECT statement in Oracle 11G
  17. SELECT INTO using Oracle
  18. SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
  19. `show create table` equivalent in oracle sql
  20. Does ‘Select’ always order by primary key?
  21. Query json dictionary data in SQL
  22. ORA-00972 identifier is too long alias column name
  23. How to use a dynamic parameter in a IN clause of a JPA named query?
  24. Search an Oracle database for tables with specific column names?
  25. How to replace specific values in a oracle database column?
  26. ORA-01861: literal does not match format string
  27. Query a Table’s Foreign Key relationships
  28. Backup SQL Schema Only?
  29. IN vs OR of Oracle, which faster?
  30. is there a PRODUCT function like there is a SUM function in Oracle SQL?

Leave a Comment