Oracle SQL使用另一个表的值更新一个表列

Question

我有一个表A，其中有一列D_DATE，其值为YYYYMMDD（我不打扰日期格式）。我也碰巧有另一个表B，其中有一个列名称V_TILL。现在，我想更新表B的V_TILL列值，其值为表A中的D_DATE列，该列恰好也有重复值。意思是，内部查询可以从我形成查询的位置返回多个记录来更新表。

我现在有这个疑问写，但它引发错误：

ORA-01427: single-row subquery returns more than one row

UPDATE TAB_A t1 
SET (V_TILL) = (SELECT TO_DATE(t2.D_DATE,'YYYYMMDD') 
         FROM B t2 
         WHERE t1.BR_CODE = t2.BR_CODE 
         AND t1.BK_CODE = t2.BK_CODE||t2.BR_CODE) 
WHERE EXISTS (
    SELECT 1 
    FROM TAB_B t2 
    WHERE t1.BR_CODE = t2.BR_CODE 
    AND t1.BK_CODE = t2.BK_CODE||t2.BR_CODE) 

PS：BK_CODE是BK_CODE以及级联BR_CODE

请帮助我，我陷在这个泥潭！任何帮助，将不胜感激。

Answer 1

如果子查询返回许多值，您想使用哪一个值？

如果有任何你可以使用rownum < = 2; 如果您知道只有一个值，使用不同的上述

SET (V_TILL) = (SELECT TO_DATE(t2.D_DATE,'YYYYMMDD') 
         FROM B t2 
         WHERE t1.BR_CODE = t2.BR_CODE 
         AND t1.BK_CODE = t2.BK_CODE||t2.BR_CODE AND ROWNUM <=1) 

或

SET (V_TILL) = (SELECT DISTINCT TO_DATE(t2.D_DATE,'YYYYMMDD') 
         FROM B t2 
         WHERE t1.BR_CODE = t2.BR_CODE 
         AND t1.BK_CODE = t2.BK_CODE||t2.BR_CODE) 

变通办法。要做到这一点，你必须分析你为什么获得多个价值。也许更复杂的逻辑需要选择正确的值。

Answer 2

I got it working with this command: 
MERGE INTO TAB_A A 
USING TAB_B B 
ON (A.BK_CODE = B.BK_CODE || B.BR_CODE 
AND A.BR_CODE = B.BR_CODE AND B.BR_DISP_TYPE <> '0' 
AND ((B.BK_CODE, B.BR_SUFFIX) IN (SELECT BK_CODE, 
             MIN(BR_SUFFIX) 
             FROM TAB_B 
             GROUP BY BK_CODE))) 

as mentioned earlier by many, I was missing an extra condition and got it working, otherwise the above mentioned techniques work very well. Thanks to all!