Oracle - how to insert if not exists?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Here's one option: using the MINUS set operator, find missing codeclient values and then insert appropriate row(s).

Before:

SQL> select * From stats_client_Test order by codeaxestat, codeclient;

CODECLIENT           CODEAXESTAT CODEELEMENTSTAT VALEURAXESTATISTIQUECLIENT
-------------------- ----------- --------------- ----------------------------------------
1000                           5               1 JOHN
1001                           5               1 ALICE
1002                           5               1 JIM
1003                           5               1 BOB
1000                           9               1 MAN
1001                           9               1 WOMAN
1002                           9               1 unknown
1003                           9               1 MAN

8 rows selected.

Query:

SQL> insert into stats_client_test
  2    (codeclient, codeaxestat, codeelementstat, VALEURAXESTATISTIQUECLIENT)
  3  select x.codeclient, 9, 1, 'unknown'
  4  from (select codeclient from stats_client_Test
  5        where codeaxestat = 5
  6        minus
  7        select codeclient from stats_client_Test
  8        where codeaxestat = 9
  9       ) x;

0 rows created.

After:

SQL> select * From stats_client_Test order by codeaxestat, codeclient;

CODECLIENT           CODEAXESTAT CODEELEMENTSTAT VALEURAXESTATISTIQUECLIENT
-------------------- ----------- --------------- ----------------------------------------
1000                           5               1 JOHN
1001                           5               1 ALICE
1002                           5               1 JIM
1003                           5               1 BOB
1000                           9               1 MAN
1001                           9               1 WOMAN
1002                           9               1 unknown     --> here it is
1003                           9               1 MAN

8 rows selected.

SQL>