How I can modify this code to introducing just the values how not exists in another table

Question

I try to use the next code for introducing users how are in glob table, but they don't exists in tlp table, but every time when I run the program, introducing the duplicate value for one user. I don't understand why .

INSERT INTO tlp
            (given_name, 
             namel, 
             email, poreclat) 
SELECT first_name, 
       namel, 
       mail, porecla 

FROM   glob 
WHERE 


(( NOT EXISTS (SELECT email 
                     FROM   tlp 
                     WHERE  glob.mail = tlp.email ) 
         AND glob.mail IS NOT NULl)
          and glob.country='Romania'
         ) 
        or (( NOT EXISTS (SELECT namel 
                         FROM   tlp 
                         WHERE  glob.namel = tlp.namel
                        ) 
                      and  glob.country='Romania'))
        or ( exists (select given_name
                  from tlp
                  where (glob.first_name not like '07_%') and 
                 glob.first_name not like 'car%' and
                 glob.first_name not like 'TR_%'
                 )
                    );

here I created the tables

create table glob
 ( persone_id number not null ENABLE,
 first_name varchar(10),
 namel varchar(10),
 mail varchar(50),
 country varchar(10),
 porecla varchar (10),

  create_date date default sysdate);

  insert into glob(persone_id,first_name, namel, mail) values (1, 'daniela', 'pop', 'dany_pop93@yahoo.com');
  insert into glob(persone_id,first_name, namel,mail, country) values (2, 'razvan', 'cirstescu', null, 'Romania');
  insert into glob (persone_id,first_name, namel, country) values (3, 'darius', 'moldovan', 'Germany' );
  insert into glob (persone_id,first_name, namel, mail, country) values  (4, 'alex', 'duta', null, 'Romania');
  insert into glob(persone_id,first_name, namel, mail, country) values (5, 'edith', null, 'edith@yahoo', 'Romania');
  insert into glob(persone_id,first_name, namel, mail) values (6, 'madi', 'todea', 'madi@yahoo');
  insert into glob(persone_id,first_name, namel, mail) values (7, 'madi', 'todea', 'madi@yahoo');
  insert into glob(persone_id,first_name, namel, mail) values (8, 'madi', 'todea', 'madi@yahoo');

the second table is:

create table tlp
  ( given_name varchar(10),
   namel varchar (10),
   email varchar (50),
   poreclat varchar(10),

   create_date date default sysdate);

   insert into TLP(given_name, namel, email) values ('daniela', 'pop', 'dany_pop93@yahoo.com');
 insert into TLP(given_name, namel, email) values ('andrei', 'pop', 'andrei@yahoo');

And now I observed, this code introducing duplicate values .

My question is : How I can make this code to not introducing duplicate values ?

Answer 1

Try to use distinct + updated where conditions

 INSERT INTO tlp
            (given_name, 
             namel, 
             email, poreclat) 
SELECT DISTINCT first_name, 
       namel, 
       mail, porecla 

FROM   glob 
(( NOT EXISTS (SELECT email 
                     FROM   tlp 
                     WHERE  glob.mail = tlp.email ) 
         AND glob.mail IS NOT NULl)
          and glob.country='Romania'
         ) 
        or (( NOT EXISTS (SELECT namel 
                         FROM   tlp 
                         WHERE  glob.namel = tlp.namel
                        ) 
                      and  glob.country='Romania'))
        or ( exists (select given_name
                  from tlp
                  where (glob.first_name not like '07_%') and 
                 glob.first_name not like 'car%' and
                 glob.first_name not like 'TR_%'
                 )
)
and (first_name, namel, mail, porecla) not in (select given_name, namel, email, poreclat from tlp )

Answer 2

First of all you should create an unique constraint to avoid any duplicate rows from being inserted.

SQL> ALTER TABLE tlp ADD CONSTRAINT tlp_unq UNIQUE (given_name, namel, email, poreclat, create_date);

Table altered.

Use MERGE to do an upsert, it is easy to understand and quite verbose. The only thing to keep in mind is use DISTINCT to pick distinct rows from glob table. The problem is that you already have duplicate rows in your source table glob.

SQL> MERGE INTO tlp d
  2  USING (SELECT DISTINCT first_name, namel, mail, porecla, create_date FROM glob) s
  3  ON(d.given_name = s.first_name) -- join key(s)
  4  WHEN MATCHED THEN
  5     UPDATE SET d.namel = s.namel,
  6                d.email = s.mail,
  7                d.poreclat = s.porecla,
  8                d.create_date = s.create_date
  9  WHEN NOT MATCHED THEN
 10  INSERT (given_name,
 11               namel,
 12               email, poreclat, create_date)
 13  VALUES (s.first_name,
 14         s.namel,
 15         s.mail, s.porecla, s.create_date );

6 rows merged.

Let's verify the table:

SQL> SELECT * FROM tlp;

GIVEN_NAME NAMEL      EMAIL                PORECLAT   CREATE_DATE
---------- ---------- -------------------- ---------- -----------
alex       duta                                       11-AUG-15
madi       todea      madi@yahoo                      11-AUG-15
edith                 edith@yahoo                     11-AUG-15
darius     moldovan                                   11-AUG-15
razvan     cirstescu                                  11-AUG-15
daniela    pop        dany_pop93@yahoo.com            11-AUG-15
andrei     pop        andrei@yahoo                    11-AUG-15

7 rows selected.

SQL>

Update OP wants to know how to filter the rows, i.e. conditional INSERTS and UPDATES using WHERE clause.

You could simply add the WHERE clause to the update and insert statements:

For example

MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

See more examples here.