Modification update between two Oracle tables

Asked

Viewed 2,482 times

1

I need to make an integration between two sws of the house and I need help.

I have an integration table (3rd) that will receive the data of a new management, if created or modified.

For this, I have a view that brings the source data and I have a table with all management registered at the destination to compare.

The idea is to make a comparison between origin and destination. If there is a management name in origin that does not exist in the destination, compare the ids. If the Id does not exist, enter the information in a 3rd table. If the Id exists, enter the same data in the 3rd table. The source table can return numerous rows in this comparison.

My problem lies with comparison. I cannot program so that I only have 1 row corresponding to the ID and the new Management, which will be inserted in my 3rd table.

sql oracle

  • Data is missing in my opinion but a Stored Procedured can solve the problem.

  • I agree. It’s just that all the programming I’ve done is in the client’s environment and inaccessible. So I just described the logic. Yes, it will be a trial, but I couldn’t develop her logic.

2 answers

3


The idea is to make a comparison between origin and destination. Case there is a management name at source that does not exist at the destination, compare ids. If Id does not exist, enter the information in a 3rd table. If the Id exists, enter these same data in the 3rd table. A source table can return numerous rows in this comparison.

CREATE OR REPLACE PROCEDURE SP1 IS
  VN_NOME DESTINO.NOME%TYPE;
BEGIN
  FOR RORIGEM IN (SELECT ID,NOME FROM ORIGEM)
  LOOP
    BEGIN
      SELECT NOME INTO VN_NOME 
      FROM DESTINO WHERE ID = RORIGEM.ID;
      IF VN_NOME <> RORIGEM.NOME THEN
      END IF;
      UPDATE DESTINO SET NOME = RORIGEM.NOME
      WHERE ID = RORIGEM.ID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        INSERT INTO TABELA3 (ID,NOME) VALUES (RORIGEM,.ID,R.ORIGEM.NOME);
      WHEN OTHERS THEN
        RAISE;
    END;
  END LOOP;
END;

I think it would be more or less this.

  • Motta, that’s right. With the appropriate changes the logic worked perfectly. Thank you.

0

       update RDESTINO a
          set (a.nome) = 
              (select b.nome                 
                  from RORIGEM b
                where a.chave = b.chave)
          where a.documento = variavel and
          Exists (select * from RORIGEM b 
                     where (a.chave = b.chave));

Below is an example of another syntax, which works well and is very fast, can be used several tables in Select. I put a variable in the Where to table of RDESTINO and filtering by a key in the table RORIGEM, if not necessary, just take the variable and keep Where Exists (...)

I hope I’ve helped

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.