Home » SQL & PL/SQL » SQL & PL/SQL » MERGE...USING 'nothing' INSERT...UPDATE (Oracle 9)
MERGE...USING 'nothing' INSERT...UPDATE [message #328578] Fri, 20 June 2008 09:31 Go to next message
aangelo
Messages: 11
Registered: April 2005
Location: Gorgonzola, Italy
Junior Member
Hi all!

I want to update/insert some values into a table that contains no rows or only 1 row, and there is no key to use.
I solved with the following code (and it works fine):
MERGE INTO mytable t 
USING DUAL ON (1=1)
WHEN MATCHED THEN UPDATE 
SET fatt_a = 10
,   fatt_b = 20
WHEN NOT MATCHED THEN
  INSERT (fatt_a, fatt_b)
  VALUES (100, 200);
Does exist a cleaner way to do this?
Thanks!
--
Antonio
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328581 is a reply to message #328578] Fri, 20 June 2008 09:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's the pre-merge approach:

UPDATE the table, check SQL%ROWCOUNT after the update.
If that = 0 then INSERT
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328586 is a reply to message #328581] Fri, 20 June 2008 09:51 Go to previous messageGo to next message
aangelo
Messages: 11
Registered: April 2005
Location: Gorgonzola, Italy
Junior Member
Hello JRowbottom.

Yes, you are right, but I would like to have a single statement (if possible) to manage.
In the first way I prepared the whole statement dynamically, without the need of additional IF...THEN statements.

Bye!
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328588 is a reply to message #328581] Fri, 20 June 2008 09:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you really want to use merge, you can do it this way:
create table merge_test (col_1 varchar2(10), col_2 varchar2(10));

insert into merge_test values ('A','B');

merge into merge_test
using (select count(*) cnt from merge_Test)
on (cnt = 1)
when matched then update set col_1 = 'C',col_2 = 'D'
when not matched then insert (col_1,col_2) values ('E','F');

select * from merge_Test;
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328591 is a reply to message #328588] Fri, 20 June 2008 10:35 Go to previous messageGo to next message
aangelo
Messages: 11
Registered: April 2005
Location: Gorgonzola, Italy
Junior Member
Yes, this is quite good: I certainly use that.
Many thanks!
Bye.
--
Antonio
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328592 is a reply to message #328591] Fri, 20 June 2008 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should not yours is far better.

Regards
Michel
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328849 is a reply to message #328592] Mon, 23 June 2008 01:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Fri, 20 June 2008 17:39
You should not yours is far better.

Regards
Michel


Wow. Not just better, but even far better.
Better, based on what?
I think JRowbottoms exaple is more intuitive, it shows what you want to check (is there a row in the target table).
Since the target table contains only 0 or 1 row(s), performance is not an issue either here.
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328854 is a reply to message #328849] Mon, 23 June 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Better, based on what?

In performances.
"DUAL" is no access, "1=1" is no test.
"select count(*) cnt from merge_Test" is scan the table (and maybe undo segment and maybe more) and count, "cnt=1" is a real test.

The prime rule of a good develoment is:
you don't do anything that is not absolutly necessary.

Regards
Michel

[Updated on: Mon, 23 June 2008 01:51]

Report message to a moderator

Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328857 is a reply to message #328854] Mon, 23 June 2008 01:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not true.
The prime rule of good development is you write something that is maintainable. If that means using a less efficient algorithm, then so be it, as long as the loss in efficiency is within reasonable boundaries.
This example most definitely is within such boundaries (unless the query is executed at an insane frequency).
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328862 is a reply to message #328857] Mon, 23 June 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think that
"using (select count(*) cnt from merge_Test) on (cnt = 1)"
Is more maintanable than
"USING DUAL ON (1=1)"?

When I see the first one I think, weel we must count the rows and see if it is 1 and I wonder what happens if it is 0, 2 or more and have to analyze...
When I see the latter one is the same thing when I see "from dual" in select; it just mean always true always 1 row.
Maybe you are not used to see it but in few time it will be (if it is used) as common as "from dual" to get a single value from an expression.
Maybe one day Oracle will put the "using" clause optional and in this case it would be far easier to know that "using dual on (1=1)" can be removed than the first condition that will vary in each statement of this kind.
Doesn't it more maintanable to have a single expression to say "useless"?

Regards
Michel
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328866 is a reply to message #328862] Mon, 23 June 2008 02:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The reason why the select on the table is more intuitive (at least to me) is that it shows me that we check the contents of that table.
I must admit that I am not really used to using merge (have been doing mainly Java for the last few years; almost no db-development anymore) but a first glance at the dual-version tells me exactly your point: dual plus (1=1) means "always true", so the "when not matched" will never be executed.
Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328870 is a reply to message #328866] Mon, 23 June 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was a shortcut.
It really means "always true unless there is no row", so not matched/insert is executed is there is currently no row.

Ok, if it is difficult to understand, nothing prevent from adding a comment:
MERGE INTO mytable t 
-- The following USING clause means
-- * Update existing row if it exists
-- * Otherwise insert a row
USING DUAL ON (1=1)
--
WHEN MATCHED THEN UPDATE 
SET fatt_a = 10
,   fatt_b = 20
WHEN NOT MATCHED THEN
  INSERT (fatt_a, fatt_b)
  VALUES (100, 200);

Regards
Michel

Re: MERGE...USING 'nothing' INSERT...UPDATE [message #328876 is a reply to message #328870] Mon, 23 June 2008 03:37 Go to previous message
aangelo
Messages: 11
Registered: April 2005
Location: Gorgonzola, Italy
Junior Member
Hi all!

First, thanks to all for all suggestions and remarks.

Personally, I do prefer the test on count instead of the useless (1=1).
The table should contain zero or one row but the former test allows a real control over its effective content.
I agree with all considerations about execution time but I expect that the table contains only one row (in absence of really heavy bugs...) and, in my specific case, that fragment of procedure will be executed only one time each day.
Apart all considerations, I think I'll use the statement with test on count=1.

Again, thanks to all for the discussion.
--
Antonio
Previous Topic: Index
Next Topic: Get past history data of 5 years from present data
Goto Forum:
  


Current Time: Fri Dec 02 18:55:00 CST 2016

Total time taken to generate the page: 0.28931 seconds