• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multiple Insert & Update in Oracle Merge Statement

 
Rithanya Laxmi
Ranch Hand
Posts: 185
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Is it possible we have multiple insert and update as highlighted in BOLD in MERGE statement as these insert and update are specific to different tables

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 (ID, NAME, ADDRESS) values (...)
insert ANOTHER_TABLE(NAME, ADDRESS, CONTACT) values(...)
else
update NEW_TABLE
update ANOTHER_TABLE

Thanks
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't think it can be done with a single MERGE statement. It's syntax certainly doesn't support multiple tables to be inserted into/updated at once.

I had thought it might be theoretically possible using a join between the two tables, but Oracle documentation states that even in case of updatable join view, only one table can be modified by a statement.

So, I guess you'll have to do it in two separate statements. It should still be faster than procedural code, though.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please review the replies to your previous post. It does not seem like you can do this.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic