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

PostgreSQL single query updates multiple rows

This article explores how to update multiple rows of data using a single query in a PostgreSQL database. Although PostgreSQL does not support updating multiple rows directly, there are several ways to achieve this goal.

One approach is to use a combination of the UPDATE ... FROM syntax and a mapping table. This allows multiple columns to be updated and provides greater flexibility. The following code demonstrates this approach:

<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>

More columns can be added as needed:

<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>

Another, cleaner approach is to use PostgreSQL's INSERT ... ON CONFLICT ... UPDATE syntax, which combines insert and update functionality into a single query. However, this method requires creating a unique index on the column that identifies the row to be updated.

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