How Can I Update Multiple Rows in PostgreSQL with a Single Query?

Efficiently Updating Multiple Rows in PostgreSQL

PostgreSQL offers powerful methods for updating multiple rows simultaneously, avoiding the limitations of standard update statements. This guide demonstrates a highly effective approach.

Method: Leveraging the UPDATE ... FROM Clause

The UPDATE ... FROM syntax provides a clean and efficient way to update multiple rows based on data from another source. This "mapping table" approach simplifies the process of updating multiple columns at once.

For example, to update column_a values based on corresponding column_b values:

<code class="language-sql">UPDATE test AS t
SET column_a = c.column_a
FROM (VALUES
    ('123', 1),
    ('345', 2)
) AS c(column_b, column_a)
WHERE c.column_b = t.column_b;</code>

This query uses a VALUES clause to create the mapping table. Expanding this to update multiple columns is straightforward:

<code class="language-sql">UPDATE test AS t
SET column_a = c.column_a,
    column_c = c.column_c
FROM (VALUES
    ('123', 1, '---'),
    ('345', 2, '+++')
) AS c(column_b, column_a, column_c)
WHERE c.column_b = t.column_b;</code>

This technique offers a scalable and flexible solution for updating multiple rows in PostgreSQL with a single, concise query.

The above is the detailed content of How Can I Update Multiple Rows in PostgreSQL with a Single Query?. For more information, please follow other related articles on the PHP Chinese website!