Insert Only first Occurence in Oracle table

duplicationinsertoracle

i have a table


CREATE TABLE PEOPLE
(
NAME VARCHAR(64 BYTE)PRIMARY KEY NOT NULL,
TIME TIMESTAMP(6),
NICKNAME VARCHAR2(16 BYTE),
STATE VARCHAR2(50 BYTE),
CITY VARCHAR(128 BYTE)
)

I want to insert in this table values from other tables, but for the columns NAME, the reference table can have duplicate NAME, in my TABLE PEOPLE, i want it to INSERT only the first occurence of NAME, and discard when it already exist.

I tried this


INSERT 
INTO PEOPLE( NAME, TIME,NICKNAME, STATE)
SELECT 
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME 
LEFT JOIN LIEN l ON l.NAME = p.NAME 
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00'
order by p.TIME desc

But i still get oracle error : Duplicate Key Errors (ORA-00001)
.

Thx for any help

Best Answer

MERGE INTO PEOPLE e using 
(SELECT 
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME 
LEFT JOIN LIEN l ON l.NAME = p.NAME 
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00') d
on (e.name = d.name)
WHEN NOT MATCHED THEN
  INSERT  (NAME, TIME,NICKNAME, STATE) VALUES (d.name, d.time, d.nickname, d.state);

Or use the ignore_row_on_dupkey_index hint.

INSERT /*+ ignore_row_on_dupkey_index(people(name)) */
INTO PEOPLE( NAME, TIME,NICKNAME, STATE)
SELECT 
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME 
LEFT JOIN LIEN l ON l.NAME = p.NAME 
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00'
;
Related Question