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

PostgreSQL: Streamlining Multiple Row Updates

PostgreSQL offers efficient methods for updating multiple rows simultaneously, a significant advantage when dealing with substantial datasets or intricate update conditions. Unlike traditional SQL's individual UPDATE statements per row, PostgreSQL provides two superior alternatives:

Approach 1: Conditional Updates with CASE

The CASE expression enables assigning different values to rows based on specific criteria. The UPDATE statement structure is:

UPDATE table_name
SET column_name = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE default_value
END
WHERE condition;

For instance, to set column_a to 1 where column_b is '123' and to 2 where column_b is '345':

UPDATE table_name
SET column_a = CASE
    WHEN column_b = '123' THEN 1
    WHEN column_b = '345' THEN 2
    ELSE column_a  -- Retain original value if condition not met
END
WHERE column_b IN ('123', '345');

Approach 2: UPDATE...FROM for Concise Updates

The UPDATE...FROM construct uses a separate table or subquery to define update values, resulting in cleaner, more readable code. The syntax is:

UPDATE table_name
SET column_names = (SELECT column_names FROM subquery)
WHERE condition;

Applying this to the previous example:

UPDATE table_name 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;

These PostgreSQL techniques significantly improve efficiency and code clarity compared to individual UPDATE statements, especially for large-scale data manipulation.

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