Merge-8i??

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 08 Mar 2006 9:21 am

Guys, I have the following merge below, however the version of oracle is 8, I would like to know how this migro merge to version 8.

Code: Select all
merge into bx_tbl_agencia bx_ag
using (select distinct party_name,
                       account_number,
                       location 
       from VIEW_GOL_AR_BAIXA_OPENSKIES
      ) v_ag
on ( v_ag.account_number = bx_ag.account_number  and v_ag.location = bx_ag.location )
when not matched then insert (bx_ag.id,
                              bx_ag.party_name,
                              bx_ag.account_number,
                              bx_ag.location)
values (seq_agencia.nextval,
        v_ag.party_name,
        v_ag.account_number,
        v_ag.location);
izaura
Location: santos-sp

Poston Mon, 06 Jan 2014 2:05 pm

Jade, I'm updating this old topic open with a reply in case some forista encountering this problem in ORACLE 8.

Please refer to this link for more information: http://www.tek-tips.com/viewthread.cfm?qid=949305 Hugs, Sergio Coutinho
stcoutinho
Location: Sao Paulo - SP

Poston Thu, 23 Jan 2014 5:40 pm

Guys, the Merge is a statement that is part of the ANSI standard 2003 and Oracle 8 and 8i are not adhering to this standard. Merge only works in Oracle 9i or higher version. To make the corresponding processing is necessary to create a PL/SQL block with the structure
Code: Select all
SELECT ...  IF EXISTE THEN UPDATE ... ELSE INSERT .... END IF;
, or write a similar command structure within the application.

[] s
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Fri, 24 Jan 2014 7:12 am

Updating ...

The note (link) mentioned earlier already suggests a proper syntax:
Code: Select all
BEGIN
   UPDATE my_table SET some_column = some_value
    WHERE some_condition_is_true;
   IF (sql%ROWCOUNT = 0) THEN
      INSERT INTO my_table .......;
   END IF;
END;
Hugs, Sergio
stcoutinho
Location: Sao Paulo - SP



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests