Question about Merge

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
  

Poston Mon, 24 Jan 2005 6:24 am

Hello could someone explain to me in detail what the merge command, and send me an exemplo.obrigado.david
david ribeiro guilherme
Location: Catanduva - SP

David Ribeiro Guilherme
Programador
ribeiro131@hotmail.com

Poston Mon, 24 Jan 2005 9:55 am

would be the MERGE JOIN?
MuLtAnI
Location: Videira - SC


Poston Mon, 24 Jan 2005 10:45 am

SO I DON'T KNOW HOW TO EXPLAIN IT WELL, BUT IT SEEMS HE DOES AN INSERT WITH AN UPDATE, vocês HAVE AN EXAMPLE.
david ribeiro guilherme
Location: Catanduva - SP

David Ribeiro Guilherme
Programador
ribeiro131@hotmail.com

Poston Tue, 25 Jan 2005 7:43 am

MERGE INTO table_name USING D (select number, str from test) S ON (s. number = d. number) WHEN MATCHED THEN UPDATE SET d. Field_Name = S.str WHEN NOT MATCHED THEN INSERT (d. Field_Name) VALUES (S.str.);

in this very simple example is the destination table that will suffer the insert or update, and this with an alias, the alias S test is another table where they fetched the info to the merge command.
in clause on puts the condition to the insert or update, in case if the number in S is equal to that of D update é generated, otherwise the insert.

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

Falows ...
Fabiano

Poston Thu, 30 Aug 2012 6:55 pm

William david ribeiro, the Merge is an SQL statement that was included in the Ansi SQL standard 2003 and which can be used to INSERT or UPDATE (either) and even to DELETE. He's great to optimize performance of data loads where you have to check the data of the target table to decide whether to do INSERT or UPDATE. The script that Fabiano showed is a good example, but if you want to learn in more detail this technique and many others to tune up SQL statements, SQL Tuning training suggest that will provide 15 days, 22 and 9/29/2012. More information: http://www.fabioprado.net/p/sql-tuning- ... racle.html [] s Fabio Prado
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Fri, 28 Sep 2012 10:40 am

Sorry I'm uploading a 2005 topic, but as had been done a month ago, I think I can follow on the same topic.

I'm trying to use the MERGE, however within a trigger.

The difference is that instead of using INSERT and UPDATE I'm trying to use the variables: NEW and OLD, but accuses me an error that lack key word, or something.

You can use the merge for this situation?

Follows the example code below:
Code: Select all
merge INTO contrato c
USING (
            SELECT cd_fornecedor,
                   nr_cgc_cpf
              FROM fornecedor
          ) f
    ON (c.cd_forncedor = f.cd_fornecedor)
    WHEN matched THEN :NEW.cd_fornecedor := f.cd_fornecedor
    WHEN NOT matched THEN
       :NEW.cd_fornecedor := novo_fornecedor(:NEW.nm_responsavel_financeiro,:NEW.tp_contrato,:NEW.ds_endereco_cobranca,:NEW.nr_cpf_cgc,:NEW.nm_cidade,:NEW.nm_uf); -- essa funcao cria um fornecedor novo e retorna o Id do mesmo.
     
After the THEN MATCHED condition is signaled the following error: PL/SQL: ORA-00905: keyword not found Anyone know help me?

Thank you.
jks1903

Poston Sun, 07 Oct 2012 10:07 am

Hi jks1903, You would post the DDL script and trigger tables (even if this compile error)?
Might be easier for the staff to give an opinion.

Hugs, Sergio Coutinho
stcoutinho
Location: Sao Paulo - SP

Poston Tue, 09 Oct 2012 9:03 am

[quote = " STCOUTINHO "]Hi jks1903, You would post the DDL script and trigger tables (even if this compile error)?
Might be easier for the staff to give an opinion.

Hugs, Sergio Coutinho [/quote] Sergio, good morning. I'vê been doing a little research on Oracle documentation and from what I'vê seen I think that the way I was doing Oracle does not allow, at least in its version 10 g. If so, I decided a different approach. But the question was how to use the way I was doing as the error displayed is keyword not found.

But thanks for the reply.
jks1903


  • See also
    Replies
    Views
    Last Post


    Return to DBA Tuning

    Who is online

    Users browsing this forum: No registered users and 1 guest