• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle Merge Statement for insert and update

 
Ranch Hand
Posts: 255
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I have the below query which is used to perform an insert or update based on the data retrieved from "NEW_TABLE", how can we rewrite the same using Oracle MERGE statement? Please clarify.

for TESTTABLE1 in (select NAME, ADDRESS, TESTTABLE1.ID from TESTTABLE1, TESTTABLE2 testtable2
where TESTTABLE1.ID = testtable2.ID)
loop
begin
select tableID into tableId from NEW_TABLE where ID = TESTTABLE1.ID and rownum=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
tableID := NULL;
end;
if tableID is null then
insert NEW_TABLE
else
update NEW_TABLE

Thanks.
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think a good starting point would be the documentation of the MERGE statement.
 
Rithanya Laxmi
Ranch Hand
Posts: 255
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just I want to know since we are not using any table name for INSERT & UPDATE for MATCHED and NOT MATCHED for MERGE STATEMENT, in which table the records will be inserted and updated? Also if I have multiple INSERT and UPDATE statements under MATCHED and NOT MATCHED specific to different tables , how it will work as we wont specify the table name? For example :-

 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
1. Use code tags around your (pseudo-)code as it makes it easier to read.

2. Your pseudo-code is rather confusing and mixes up various queries and inserts/updates against various tables. It would probably make sense to step back and describe clearly what you are looking for, and what you want to happen.

3. Oracle's MERGE statement is an "UPSERT" i.e. you tell it how to match records in a specific table. If the record does not exist, it will be inserted, otherwise it will be updated:



In the above example from the Oracle MERGE documentation, you should be able to see how it works.

  • We are merging into the bonuses table.
  • Our source data (in the USING clause) is queried from the employees table for department_id 80.
  • A record matches in the bonuses table if it has the same employee_id as the source record.
  • If the employee_id matches, then we update the existing record in the bonuses table and set the new bonus value.
  • If there is no matching record, then we insert the required data into the bonuses table.

  • This is one example of the MERGE syntax that illustrates how much logic you can encapsulate in a single SQL statement that can be executed efficiently on the database without fetching data into your Java application.
     
    Bartender
    Posts: 598
    26
    Oracle Notepad Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Rithanya Laxmi wrote:Just I want to know since we are not using any table name for INSERT & UPDATE for MATCHED and NOT MATCHED for MERGE STATEMENT, in which table the records will be inserted and updated? Also if I have multiple INSERT and UPDATE statements under MATCHED and NOT MATCHED specific to different tables , how it will work as we wont specify the table name? For example :-



    A few notes:

    WHEN NO_DATA_FOUND THEN
    tableID := NULL;



    That does absolutely nothing, as, if no record was returned, it would already be NULL. Remove that line of code. It does nothing.

    It is generally not a good idea to store a "table id" in a table. It just confuses the code. Either the SP should decide what to do (and it does not need to be passed a "table id") or, if the code is aware of the required table, there should be two separate SPs, and the code should call the correct one.

    This code wants to INSERT INTO two TABLEs, which is accomplished via an INSERT ALL (and UPDATE one of them), or it wants to INSERT INTO one table, and MERGE INTO another. Most likely, the MERGE is a better idea, as it will only acquire the lock on the table's record(s) once.

    To decide whether to INSERT o not, use a WHERE clause. That's what it's for. Using procedural code just slows things down.

    The FOR is most likely not required. In created an implicit CURSOR, and invokes the PL/SQL engine where SQL itself ought to work just fine.

    Naming a variable the same name as the table it is getting code from is a bit confusing.

    You cannot use a variable for a table name outside of dynamic SQL. Dynamic SQL is insecure and should not be used.

    ----------

    I am guessing that you want to write a generic SP to UPSERT into any given table. Please do not do this. It is bad design. If you must have one SP to rule them all, specify each TABLE separately. And include the COLUMN list. Besides being self-documenting, it'll save you from other bugs due to added or reordered columns.

     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    reply
      Bookmark Topic Watch Topic
    • New Topic