Doubts about Merge

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
Post Reply
david ribeiro guilherme
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Tue, 11 Jan 2005 7:44 am
Location: Catanduva - SP
David Ribeiro Guilherme
Programador
ribeiro131@hotmail.com

Good morning, someone could explain to me with details for what the Merge command is, and send me an example. Thank you.David
User avatar
MuLtAnI
Moderador
Moderador
Posts: 90
Joined: Tue, 01 Jun 2004 9:21 am
Location: Videira - SC
Contact:

Was Merge Join?
david ribeiro guilherme
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Tue, 11 Jan 2005 7:44 am
Location: Catanduva - SP
David Ribeiro Guilherme
Programador
ribeiro131@hotmail.com

So I can not explain well, but it looks like it does an insert along with an update, you have an example.
Fabiano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Wed, 23 Jun 2004 2:18 pm

Select all

MERGE INTO nome_da_tabela D 
   USING (select numero, str from teste) S 
   ON (S.numero = D.numero) 
   WHEN MATCHED THEN UPDATE SET D.nome_campo = S.str 
   WHEN NOT MATCHED THEN INSERT (D.nome_campo) 
   VALUES (S.str);
In this very simple example the target table is the one that will suffer the insert or update, and this with an alias d, the test with alias s is another table where it will be Info are sought for command Merge.
In the Clausula ON places the condition for the INSERT or UPDATE, if the N number is equal to that of D o UPDATE IS GENED, SEINAO.

está very simple but I hope it helps you ...

falows ...
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

David Ribeiro Guilherme,

The merge is an SQL statement that has been included in ANSI SQL 2003 standard and that can be used to perform insert or update (one or one) and even to make delete. It is great for optimizing performance of data loads where you have to check the data from the target table to decide whether to do insert or update. The fabiano script presented is a good example, but if you want to learn in more detail this technique and many others to tune SQL instructions, I suggest the training of SQL Tuning that I will minister on 15, 22 and 29/09/2012. More information: http://www.fabioprado.net/p/sql-tuning- ... racle.html

S

Fábio Prado
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 188
Joined: Thu, 04 Feb 2010 8:08 am

Sorry to be up to a topic of 2005, but as you had already been done a month ago I think I can follow the same topic.

I'm trying to use the merge, but inside a trigger.

The difference is that instead of using INSERT and UPDATE I am trying to use the variables: new and: old, but it accuses me a mistake that lacks key word, or something.

Is it possible to use the merge for this situation?

follows the sample code below:



After the Matched condition then, the following error is signaled:

PL / SQL: ORA-00905: Keyword not found

Someone know how to help me?

Thanks.
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Hi JKS1903,

Would you like to post the DDL script of tables and trigger (even if this compile with error)?
Maybe it's easier for people to give an opinion.

Hugs,

Sergio Coutinho
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 188
Joined: Thu, 04 Feb 2010 8:08 am

stcoutinho wrote: Hi JKS1903,

Would you like to post the DDL script of tables and trigger (even if this compile with error)?
Maybe it's easier for people to give an opinion.

Hugs,

Sergio Coutinho
Sergio, Good morning.
I'vê been researching a bit in Oracle's documentation and from what I'vê seen I think the way I was doing Oracle does not allow, at least in its 10G version. In case, I solved it in another way. But the doubt was how to use the way I was doing, because the error presented is a keyword not found.

But thanks for the answer.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 5 guests