Doubt insert

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

Poston Thu, 18 Oct 2007 8:02 am

Good morning folks.

This insert takes information from a table and checks if the COD_TRAT_CC exists in the tables TAB_CC and TABELA_TRATAMENTO, if the code exists in one of the two tables and should not enter. However he continues to insert the code that already exists in the table TAB_CC on TABELA_TRATAMENTO, what am I doing wrong?


Code: Select all
insert into TABELA_TRATAMENTO
           select distinct '13',
                  COD_TRAT_CC,
                  W_DATA,
                  'I'
           from CONTA_CC
           where COD_TRAT_CC not in
          (select COD_CC from TAB_CC) and
           COD_TRAT_CC not in
           (select COD_PROD from TABELA_TRATAMENTO where TAB = '13')
Thanks!
KRECCHI
krecchi
Location: São Paulo

KRecchi

Poston Thu, 18 Oct 2007 8:34 am

Attempts to make union, I think it is easier to understand the business logic.

Code: Select all
where COD_TRAT_CC not in
          (select COD_CC from TAB_CC
           UNION
           select COD_PROD from TABELA_TRATAMENTO where TAB = '13')
or NOT EXISTS, I think it's faster:
Code: Select all
select
  distinct '13'
, x.COD_TRAT_CC
, x.W_DATA
, 'I'
from CONTA_CC x
where NOT EXISTS (select 1
                  from TAB_CC
                  where COD_CC = x.COD_TRAT_CC)
  and NOT EXISTS (select 1
                  from TABELA_TRATAMENTO
                  where COD_PROD = x.COD_TRAT_CC
                    AND TAB = '13')
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 1 guest