Oracle SQL选择唯一值AND只匹配具有非唯一值的行的一个值

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.

如您所见,项目124有2个描述。哪种描述无关紧要。

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.

我的尝试是使用两个代码返回124,我理解我的代码正在寻找主机中的唯一代码和描述,这就是为什么它返回124但我不确定如何修复它。

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

1 个解决方案

#1

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

Unique会筛选所有列中SELECTed结果集中的重复条目,而不仅仅是一个键。

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().

如果要提取已过滤的键的其他属性,则必须指示数据库首先对唯一键进行分组。要选择分组键的属性之一,我们可以使用AGGREGATE函数。像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.

有分析功能,可用于甚至复杂的要求。

#1