Updating Rows With a Subquery Referencing the Same Table in SQL

1. Overview

Subqueries are SELECT statements nested inside other SQL statements. Such nested queries execute first and return a result, which is then used by the outer query.

In this tutorial, we’ll demonstrate how to update rows using a subquery that references the same table in a MySQL relational database. Then, we’ll demonstrate that we can update rows via an UPDATE … FROM statement referencing the same table in both the PostgreSQL and the SQL Server relational databases.

Notably, we’ll employ the Program table from the Baeldung University database.

2. MySQL Database

Let’s suppose we want to update the end_date in the Program table to equal the start_date + 3 weeks for all rows. To achieve this, we have two main options.

At the outset, we can’t use a simple UPDATE … FROM statement in MySQL to access the outer query table within a subquery in an UPDATE statement. In fact, when we try to do this, we encounter ERROR 1093 (HY000): You can’t specify the target table ‘x’ for update in the FROM clause.

2.1. Using a Subquery Within a Self-Join

We can update rows using a subquery that references the same table through a self-join. This method is especially useful if parts of the updated data come from within the same table, e.g., updating the end_date column based on the start_date column:

UPDATE Program AS P1
JOIN (
    SELECT id, DATE_ADD(start_date, INTERVAL 3 WEEK) AS new_end_date
    FROM Program
) AS P2
ON P1.id = P2.id
SET P1.end_date = P2.new_end_date;

In this case, the statement above joins the Program table with a temporary table (P2) that contains the calculated new_end_date for every row. The UPDATE then sets the end_date of each row in P1 to the corresponding value from P2.

Thus, we update all 24 rows in the table. Let’s verify this with a SELECT query on the table:

SELECT start_date,end_date from Program;
+------------+------------+
| start_date | end_date   |
+------------+------------+
| 2012-01-01 | 2012-01-22 |
| 2012-01-01 | 2012-01-22 |
| 2011-01-01 | 2011-01-22 |
| 2011-01-01 | 2011-01-22 |
| 2010-01-01 | 2010-01-22 |
| 2010-01-01 | 2010-01-22 |
| 2018-07-01 | 2018-07-22 |
| 2018-07-01 | 2018-07-22 |
| 2011-01-11 | 2011-02-01 |
| 2011-01-11 | 2011-02-01 |
| 2010-07-11 | 2010-08-01 |
| 2010-07-11 | 2010-08-01 |
| 2010-07-01 | 2010-07-22 |
| 2010-07-01 | 2010-07-22 |
| 2010-07-01 | 2010-07-22 |
| 2010-07-01 | 2010-07-22 |
| 2010-01-11 | 2010-02-01 |
| 2010-01-11 | 2010-02-01 |
| 2011-07-01 | 2011-07-22 |
| 2011-07-01 | 2011-07-22 |
| 2011-01-11 | 2011-02-01 |
| 2011-01-11 | 2011-02-01 |
| 2010-07-11 | 2010-08-01 |
| 2010-07-11 | 2010-08-01 |
+------------+------------+

As expected, all values are updated properly.

2.2. Using a Subquery Within a Derived Table

As another option, we can use a derived table for updating with data within the same table. In particular, the derived table Temp_table acts as an intermediate result set that the outer UPDATE query depends upon. Therefore, the UPDATE is performed only after the result set from the derived table is materialized:

UPDATE Program
SET end_date = (
    SELECT new_end_date FROM (
        SELECT DATE_ADD(start_date, INTERVAL 3 WEEK) AS new_end_date
        FROM Program AS Temp
        WHERE Temp.id = Program.id
    ) AS Temp_table
);

In this example, the subquery calculates the end_date for each row individually by joining the Temp_table to the Program table on the id column.

3. PostgreSQL Database

The PostgreSQL UPDATE syntax is more flexible and doesn’t require a nested subquery to avoid a self-referencing error. Again, we have two options for the example task of updating the end_date column in the Program table to equal the start_date + 3 weeks for all rows.

3.1. Using a Self-Join

Even in the case of PostgreSQL, we can update all 24 rows using a self-join, but this time we don’t need a SELECT subquery:

UPDATE Program AS P1
SET end_date = P2.start_date + INTERVAL '3 weeks'
FROM Program AS P2
WHERE P1.id = p2.id;

In fact, this is perhaps the most common method for performing such an update in PostgreSQL. Specifically, it joins the Program table (aliased as P1) with itself (aliased as P2) on the id field and sets the P1 end_date to the calculated value from P2.

Let’s verify all 24 rows are updated:

postgres=> SELECT start_date, end_date from Program;
 2012-01-01 | 2012-01-22
 2011-01-01 | 2011-01-22
 2011-01-01 | 2011-01-22
 2018-07-01 | 2018-07-22
 2018-07-01 | 2018-07-22
 2010-01-11 | 2010-02-01
 2010-01-11 | 2010-02-01
 2011-07-01 | 2011-07-22
 2011-07-01 | 2011-07-22
 2010-07-01 | 2010-07-22
 2010-07-01 | 2010-07-22
 2010-07-01 | 2010-07-22
 2010-07-01 | 2010-07-22
 2011-01-11 | 2011-02-01
 2011-01-11 | 2011-02-01
 2010-07-11 | 2010-08-01
 2010-07-11 | 2010-08-01
 2011-01-11 | 2011-02-01
 2011-01-11 | 2011-02-01
 2010-07-11 | 2010-08-01
 2010-07-11 | 2010-08-01
(24 rows)

Similar to the previous example, the self-join works in practice.

3.2. Using a Subquery Within an UPDATE … SET Statement

We can use a subquery within the SET clause of the UPDATE statement:

UPDATE Program
SET end_date = (
    SELECT start_date + INTERVAL '3 weeks'
    FROM Program AS Temp
    WHERE Temp.id = Program.id
);

This query updates each row by correlating it with a subquery on the same table. Furthermore, the subquery fetches the start_date for the corresponding id and adds three weeks. Again, we can verify that the end_date is set to start_date + 3 weeks using a simple, unnested SELECT query.

4. SQL Server Database

The SQL Server UPDATE…JOIN syntax is very powerful, enabling us to join tables directly within the UPDATE statement. In this case, we use the DATEADD function for date calculations. So, let’s discuss two options to update the end_date in the Program table.

4.1. Using a Self-Join

Similar to PostgreSQL, we can use a self-join without a nested subquery:

UPDATE P1
SET P1.end_date = DATEADD(week, 3, P2.start_date)
FROM Program AS P1
JOIN Program AS P2
ON P1.id = P2.id;

This statement updates P1 using a JOIN to P2 on the id column. Specifically, the FROM clause defines the tables being used and their aliases, and the SET clause performs the update using the joined data.

4.2. Using a Subquery Within an UPDATE … SET Statement

Lastly, as an alternative, we can use a subquery within an UPDATE … SET statement:

UPDATE Program
SET end_date = (
    SELECT DATEADD(week, 3, start_date)
    FROM Program AS Temp
    WHERE Temp.id = Program.id
);

Similar to PostgreSQL, SQL Server can employ a correlated subquery to update each row based on its own data without a complex nested structure.

5. Conclusion

In this article, we learned about updating rows in a table using a subquery on the same table.

MySQL doesn’t support a straightforward UPDATE … FROM statement; therefore, we usually need a nested subquery using a self-join or a derived table. However, PostgreSQL and SQL Server can both use an UPDATE … FROM syntax to update rows using data obtained from the same table; therefore, we don’t always need a subquery.