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

Single-Query Updates in PostgreSQL: A Performance Boost

PostgreSQL, like many database systems, offers efficient methods for updating multiple rows simultaneously using a single query. This approach significantly enhances performance, particularly when dealing with substantial datasets.

The UPDATE statement with the SET clause provides a straightforward method for conditional updates. For instance, to modify column_a based on different values in column_b, you can employ this concise syntax:

UPDATE table_name
SET column_a = 1 WHERE column_b = '123',
    column_a = 2 WHERE column_b = '345';

However, for more intricate updates involving multiple columns, the UPDATE ... FROM construct offers superior flexibility. This involves constructing a temporary mapping table to specify the updates.

Let's illustrate with an example updating both column_a and column_c based on column_b:

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;

This query leverages a VALUES clause to define the mapping table c, effectively linking new values to existing rows in test based on matching column_b values. This streamlined approach allows for efficient, multi-column updates within a single query.

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