vendredi 8 mai 2015

Oracle SQL Select Unique Value AND Only one value from matching rows with non-unique values

I have two tables, a master table and a general information table. I need to update my master table from the general table. How can I update the master table when the general info table can have slightly different values for the descriptions?

Master

+------+---------+
| Code |  Desc   |
+------+---------+
|  156 | Milk    |
|  122 | Eggs    |
|  123 | Diapers |
+------+---------+

Info

+------+---------------+--------+
| Code |     Desc      | Price  |
+------+---------------+--------+
|  156 | Milk          | $3.00  |
|  122 | Eggs          | $2.00  |
|  123 | Diapers       | $15.00 |
|  124 | Shopright Cola| $2.00  |
|  124 | SR Cola       | $2.00  |
+------+---------------+--------+

As you can see item 124 has 2 descriptions. It does not matter which description.

My attempt is returning 124 with both codes, I understand my code is looking for both the unique Code and description in the master which is why it returns both 124 but I'm unsure how to fix it.

INSERT INTO MASTER
(
SELECT UNIQUE(Code), Desc FROM INFO A
WHERE NOT EXISTS
   (SELECT Code FROM MASTER B
   WHERE A.Code = B.Code )
);

I have also tried:

INSERT INTO MASTER
(
SELECT UNIQUE(PROC_CDE), Desc FROM FIR_CLAIM_DETAIL A
WHERE Code NOT IN
    (SELECT Code FROM FIR_CODE_PROC_CDE_MSTR B
    WHERE A.Code = B.Code )
);

Aucun commentaire:

Enregistrer un commentaire