This table and sample data is used in the examples below:

CREATE TABLE table_update
(
    id1 NUMBER(2,0)
,   desc1 VARCHAR(2)
,   desc2 VARCHAR(2)
);

INSERT INTO table_update (id1, desc1, desc2) VALUES (1, 'A', 'X');
INSERT INTO table_update (id1, desc1, desc2) VALUES (2, 'B', 'Y');
INSERT INTO table_update (id1, desc1, desc2) VALUES (3, 'C', 'Z');

COMMIT;

This creates a simple table with three columns: a numeric identifier and two character description fields. The sample data provides a foundation for demonstrating various UPDATE scenarios.

The basic UPDATE statement allows you to modify one or more columns in a table. The syntax includes the UPDATE keyword, the table name, a SET clause specifying the new values, and an optional WHERE clause to limit which rows are affected. Without a WHERE clause, all rows in the table would be updated.

UPDATE  table_update
SET
        id1 = 11
,       desc1 = 'V'
WHERE   id1 = 1;

This statement updates the row where id1 equals 1, changing the id1 value to 11 and the desc1 value to 'V'. The WHERE clause ensures only the specified row is modified.

Select the data from the table "table_update":

SELECT id1, desc1 FROM table_update;

Query result:

id1   desc1
----- -----
11    V
2     B
3     C

A correlated subquery in an UPDATE statement references columns from the outer table being updated. This allows you to calculate new values based on existing data in the same table or related tables. The subquery is executed once for each row being updated, with access to the current row's values through table aliases.

UPDATE  table_update t1
SET
        id1 = id1 * 10
,       t1.desc1 =
        (
                SELECT  t2.desc1 || t1.desc1
                FROM    table_update t2
                WHERE   t2.id1 = t1.id1
        )
WHERE   t1.id1 = 1;

In this example, the subquery concatenates the existing desc1 value with itself by joining the table to itself. The correlation occurs through the WHERE clause matching t2.id1 = t1.id1, ensuring the subquery processes the same row being updated.

Select the data from the table "table_update":

SELECT id1, desc1 FROM table_update;

Query result:

id1   desc1
----- -----
10    AA
2     B
3     C

Oracle allows updating multiple columns simultaneously using a single subquery that returns multiple values. This approach is more efficient than separate UPDATE statements and ensures atomicity of the operation. The syntax uses parentheses to group both the target columns and the subquery results.

UPDATE  table_update t1
SET
        (t1.desc1, t1.desc2) = (
                SELECT (t2.desc1 || t1.desc1), (t2.desc2 || t1.desc2)
                FROM table_update t2
                WHERE t2.id1 = t1.id1
        );

This statement updates both desc1 and desc2 columns by concatenating each column with itself. The subquery returns two values that correspond to the two target columns specified in the SET clause.

Select the data from the table "table_update":

SELECT id1, desc1 FROM table_update;

Query result:

id1   desc1 desc2
----- ----- -----
1     AA    XX
2     BB    YY
3     CC    ZZ

1. Common Error: Multiple Rows Returned
   A frequent error when using subqueries in UPDATE statements occurs when the subquery returns more than one row for a single row being updated. Oracle expects exactly one row from the subquery for each row being updated. This error is identified by Oracle error code ORA-01427.
   

   UPDATE  table_update t1
   SET
           (t1.desc1, t1.desc2) = (
                   SELECT (t2.desc1 || t1.desc1), (t2.desc2 || t1.desc2)
                   FROM table_update t2
           );
   

   Query result:
   

   Error starting at line 1 in command:
   UPDATE  table_update t1
   SET
           (t1.desc1, t1.desc2) = (
                   SELECT (t2.desc1 || t1.desc1), (t2.desc2 || t1.desc2)
                   FROM table_update t2
           )
   
   Error report:
   SQL Error:
   ORA-01427: single-row subquery returns more than one row
   01427.00000 -  "single-row subquery returns more than one row"
   *Cause:
   *Action:
   

   The error occurs because the subquery lacks a WHERE clause to correlate it with the outer query, causing it to return all rows from table_update for each row being updated.
2. Handling NULL Values in Subquery Results
   When a subquery returns no rows for a particular row being updated, Oracle sets the target columns to NULL.
   

   UPDATE  table_update t1
   SET
           (t1.desc1, t1.desc2) = (
                   SELECT (t2.desc1 || t1.desc1), (t2.desc2 || t1.desc2)
                   FROM table_update t2
                   WHERE t2.id1 = t1.id1
                   AND t2.id1 < 3
           );
   

   Select the data from the table "table_update":
   

   SELECT id1, desc1 FROM table_update;
   

   Query result:
   

   id1   desc1 desc2
   ----- ----- -----
   1     AA    XX
   2     BB    YY
   3
   

   Since the third row had no match in the subquery, the columns were initialized to NULL.
   Unless this behavior is intended, always add the same conditions in the subquery for the rows to be modified in the table:
   

   UPDATE  table_update t1
   SET
           (t1.desc1, t1.desc2) = (
                   SELECT (t2.desc1 || t1.desc1), (t2.desc2 || t1.desc2)
                   FROM table_update t2
                   WHERE t2.id1 = t1.id1
                   AND t2.id1 < 3
           )
   WHERE   t1.id1 < 3;
   

   Select the data from the table "table_update":
   

   SELECT id1, desc1 FROM table_update;
   

   Query result:
   

   id1   desc1 desc2
   ----- ----- -----
   1     AA    XX
   2     BB    YY
   3     CC    ZZ
   

   By adding the same condition (t1.id1 < 3) to the main UPDATE WHERE clause, we prevent the unwanted NULL assignment to the third row.
   

Oracle allows updating data through inline views, which are subqueries in the FROM clause of an UPDATE statement. This technique is useful when you need to update a subset of columns from a table based on complex criteria. The inline view must be updatable, meaning it cannot contain certain constructs like DISTINCT, GROUP BY, or aggregate functions.

UPDATE
(
        SELECT  desc1, desc2
        FROM    table_update t2
        WHERE   t2.id1 = 3
)
SET desc1 = desc1 || desc1
,   desc2 = desc2 || desc2;

This approach updates only the columns specified in the inline view SELECT list. The WHERE clause in the inline view determines which rows are eligible for update, effectively replacing the need for a WHERE clause in the main UPDATE statement.

Select the data from the table "table_update":

SELECT id1, desc1 FROM table_update;

Query result:

id1   desc1 desc2
----- ----- -----
1     A     X
2     B     Y
3     CC    ZZ