MERGE : unicité des clés de la table source

Dans un MERGE, la table appelée en source du MERGE (using) ne doit pas avoir de doublons sur sa clé de jointure.

 

SQL> CREATE TABLE nwprices (
  2     article_name VARCHAR2(20),
  3     price NUMBER,
  4     last_price CHAR(1)
  5  );

Table created.

SQL> INSERT INTO articles
  2     VALUES ('CABLE 13mm CUIVRE',15.50);

1 row created.

SQL> INSERT INTO articles
  2     VALUES ('CABLE 25mm CUIVRE',19.25);

1 row created.

SQL> INSERT INTO nwprices
  2     VALUES ('CABLE 13mm CUIVRE',15.80,'O');

1 row created.

SQL> INSERT INTO nwprices
  2     VALUES ('CABLE 25mm CUIVRE',21.05,'N');

1 row created.

SQL> INSERT INTO nwprices
  2     VALUES ('CABLE 25mm CUIVRE',21.45,'O');

1 row created.

SQL> COL price FOR 99.00
SQL> SELECT * FROM articles;

ARTICLE_NAME          PRICE
-------------------- ------
CABLE 13mm CUIVRE     15.50
CABLE 25mm CUIVRE     19.25

SQL> SELECT * FROM nwprices;

ARTICLE_NAME          PRICE L
-------------------- ------ -
CABLE 13mm CUIVRE     15.80 O
CABLE 25mm CUIVRE     21.05 N
CABLE 25mm CUIVRE     21.45 O

SQL> MERGE INTO articles ar
  2  USING nwprices nw
  3  ON (ar.article_name=nw.article_name)
  4  WHEN MATCHED
  5  THEN UPDATE
  6  SET ar.price=nw.price
  7  ;
USING nwprices nw
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables


SQL> MERGE INTO articles ar
  2  USING (
  3     SELECT * FROM nwprices
  4     WHERE last_price='O') nw
  5  ON (ar.article_name=nw.article_name)
  6  WHEN MATCHED
  7  THEN UPDATE
  8  SET ar.price=nw.price
  9  ;

2 rows merged.

SQL> SELECT * FROM articles;

ARTICLE_NAME          PRICE
-------------------- ------
CABLE 13mm CUIVRE     15.80
CABLE 25mm CUIVRE     21.45

SQL>