使用Oracle SQL在多个条件下连接两个表

Question

我有一个2个具有以下结构的表

表1--包含这样的值。

CAPACITY_CODE[/td>[/tr>[/tr>

OTHER_CODE	结果
A	1
A	5
A	9
A	(空(
B	2
B	6
B	2

这里有一把小提琴https://dbfiddle.uk/FMKdWzQT

我的查询工作正常。通过使用case语句并指定一个数字，这样我就可以使用max，然后我就删除了这个数字。

SELECT a.other_code,
a.capacity_code,
(
SELECT SUBSTR(max(
CASE WHEN b.other_code = a.other_code AND  a.capacity_code = b.capacity_code THEN  concat('3',b.myresult)
WHEN b.other_code = a.other_code AND  a.capacity_code is null and  b.capacity_code is null THEN  concat('2',b.myresult)
WHEN b.other_code = a.other_code  AND b.capacity_code in ('ELSE', 'ALL') THEN  concat('1',b.myresult)
else null end),2) 

FROM   table2 b ) as myresult
FROM   table1 a

然而，我无法让更新工作。我尝试了合并，它给了我不稳定的行错误，我尝试了更新选择，但这给了我单行子查询错误，所以也许其他人可以看看它。这是我的更新尝试。

UPDATE table1
SET myresult = (
SELECT myresult
FROM (
SELECT a.other_code,
a.capacity_code,
(
SELECT SUBSTR(max(
CASE WHEN b.other_code = a.other_code AND  a.capacity_code = b.capacity_code THEN  concat('3',b.myresult)
WHEN b.other_code = a.other_code AND  a.capacity_code is null and  b.capacity_code is null THEN  concat('2',b.myresult)
WHEN b.other_code = a.other_code  AND b.capacity_code in ('ELSE', 'ALL') THEN  concat('1',b.myresult)
else null end),2) 

FROM   table2 b ) as myresult
FROM   table1 a
)t2
WHERE table1.other_code = t2.other_code and nvl(table1.capacity_code,'x') = nvl(t2.capacity_code,'x')
);