CODEWITHSUNDEEP
sqloracle-database
JT Omer
JT Omer

Reputation: 23

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 )
);

Upvotes: 2

Views: 151

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Unique filters the duplicated entries in the SELECTed result set across all columns, not just one key.

When you want to extract the other attributes of a key you filtered, you have to instruct the database to first group the unique keys. To choose one of attributes of a grouped key, we can use an AGGREGATE function. Like MAX(), MIN().

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

There're analytical functions which can be used for even complex requirements.

Upvotes: 1

Related Questions