0

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

1
  • Use MERGE instead of INSERT without WHEN MATCHED clause.
    – Akina
    Jun 23 '20 at 13:09
2
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'
;
0

Like that:

MERGE
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 
p.TIME> '25/05/20 00:00:00'
order by p.TIME desc

I get Erreur SQL : ORA-02012 "missing USING keyword"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.