Oracle SQL Update one table column with the value of another table

Question

I have a table A, where there is a column D_DATE with value in the form YYYYMMDD (I am not bothered about the date format). I also happen to have another table B, where there is a column name V_TILL. Now, I want to update the V_TILL column value of table B with the value of D_DATE column in table A which happens to have duplicates as well. Meaning, the inner query can return multiple records from where I form a query to update the table.

I currently have this query written but it throws the error:

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 IS THE CONCATENATION OF BK_CODE and BR_CODE

Kindly help me as I am stuck in this quagmire! Any help would be appreciated.

Answer 1

If the subquery returns many values which one do you want to use ?

If any you can use rownum <=1; If you know that there is only one value use distinct

 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)

or

 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)

above are workarounds. To do it right you have to analyze why you are getting more than one value. Maybe more sophisticated logic is needed to select the right value.

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!