Oracle – Update Multiple Columns with a Single Subquery

In Oracle, it is possible to update multiple columns with data from a single subquery. The syntax is only slightly different than the typical update statement. The columns being assigned values must be enclosed by parentheses. An example follows:

SQL> DESC test1_tab;
 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 VAL1                                     VARCHAR2(20)
 VAL2                                     VARCHAR2(20)
 VAL3                                     VARCHAR2(20)

SQL> SELECT *
  2  FROM test1_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    VAL1_A               VAL2_A
B                    VAL1_B               VAL2_B
C                    VAL1_C               VAL2_C


SQL> DESC test2_tab;
 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 VAL1                                     VARCHAR2(20)
 VAL2                                     VARCHAR2(20)
 VAL3                                     VARCHAR2(20)

SQL> SELECT *
  2  FROM test2_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    TEST2_VAL1_A         TEST2_VAL2_A
B                    TEST2_VAL1_B         TEST2_VAL2_B
C                    TEST2_VAL1_C         TEST2_VAL2_C


SQL> UPDATE test1_tab t1
  2  SET ( val2, val3 ) =
  3    ( SELECT val2, val3 FROM test2_tab t2
  4      WHERE t2.val1 = t1.val1 )
  5  WHERE t1.val1 = 'B';

1 row updated.


SQL> SELECT *
  2  FROM test1_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    VAL1_A               VAL2_A
B                    TEST2_VAL1_B         TEST2_VAL2_B
C                    VAL1_C               VAL2_C
Advertisements