Home » SQL & PL/SQL » SQL & PL/SQL » MERGE with variables instead of table dat
icon4.gif  MERGE with variables instead of table dat [message #198734] Wed, 18 October 2006 06:48 Go to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
I want to know if its possible to use the MERGE command to merge between a table and variables that I created and populated inside my code.

I would like to update my table if a record exists that has the same value in the key column as I currently have in one of my variables. If it doesnt exist, I need to insert a new row into the table using some variables in my code.

Thanks
Sayuri
Cool
Re: MERGE with variables instead of table dat [message #198775 is a reply to message #198734] Wed, 18 October 2006 08:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select your variables from dual
Re: MERGE with variables instead of table dat [message #198865 is a reply to message #198775] Wed, 18 October 2006 21:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sorry Frank, I gotta step in....

I cannot think of a good reason to use the FROM DUAL work-around to perform a single row MERGE.
- The code is about the same length
- Its no more obvious whats happening than a normal INSERT/UPDATE
- Its no faster than a normal INSERT/UPDATE
- It shows you haven't given any consideration to row locking.

But by far the most important factor is that it cannot be used inside a FORALL loop. All batch DML in modern PL/SQL programs should either be set-based (ie. INSERT INTO .. SELECT), or bulk-bound inside a FORALL.

If you are performing a single row writeback inside an OLTP system, there is no harm in using MERGE (if you are writing a quick'n'nasty app that does not handle row locking), I just think it sets a dangerous precedent for batch programmers who see the technique and think it is acceptable.
[/endrant]


Ross Leishman.
Re: MERGE with variables instead of table dat [message #198898 is a reply to message #198865] Thu, 19 October 2006 01:18 Go to previous messageGo to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
I did actually try the select from dual but it didnt want to compile.

Thanks for all the advice - much oblidged.
Re: MERGE with variables instead of table dat [message #199074 is a reply to message #198865] Fri, 20 October 2006 00:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I agree Ross, I guess I was too hasty with my answer..
Re: MERGE with variables instead of table dat [message #199096 is a reply to message #199074] Fri, 20 October 2006 02:31 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's OK Frank. I'll remove you from the blacklist again. Wink
Previous Topic: Update about 7500 row running long
Next Topic: HOW TO PULL DATA FROM 2 TABLE
Goto Forum:
  


Current Time: Wed Dec 07 06:47:29 CST 2016

Total time taken to generate the page: 0.08412 seconds