Oracle - how to insert if not exists?

Question

Example dB : https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=49af209811bce88aa67b42387f1bb5f6

I'd like to add insert this line

1002 9 1 UNKNOWN

Because of the line exists

1002 5 1 JIM

I was thinking about something like select codeclient from STATS_CLIENT_TEST where CODEAXESTAT=5 and insert codeclient, 9,1,UNKNOWN.

but not sure how to do it? And simple query or a PL/SQL?

What's the best way to get it?

Thanks

Answer 1

Use an INSERT .. SELECT statement with a PARTITIONed outer join:

INSERT INTO stats_client_test (
  codeclient, codeaxestat, codeelementstat, valeuraxestatistiqueclient
)
  SELECT cc.codeclient,
         s.codeaxestat,
         s.codeelementstat,
         'UNKNOWN'
  FROM   (SELECT DISTINCT codeclient FROM stats_client_test) cc
         LEFT OUTER JOIN stats_client_test s
         PARTITION BY (s.codeaxestat, s.codeelementstat)
         ON (s.codeclient = cc.codeclient)
  WHERE  s.rowid IS NULL;

or a MERGE statement:

MERGE INTO stats_client_test dst
USING (
  SELECT cc.codeclient,
         s.codeaxestat,
         s.codeelementstat,
         s.ROWID AS rid
  FROM   (SELECT DISTINCT codeclient FROM stats_client_test) cc
         LEFT OUTER JOIN stats_client_test s
         PARTITION BY (s.codeaxestat, s.codeelementstat)
         ON (s.codeclient = cc.codeclient)
) src
ON (dst.ROWID = src.rid)
WHEN NOT MATCHED THEN
  INSERT (codeclient, codeaxestat, codeelementstat, valeuraxestatistiqueclient)
  VALUES (src.codeclient, src.codeaxestat, src.codeelementstat, 'UNKNOWN');

db<>fiddle here