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

Bulk Updating Multiple Rows with a Single PostgreSQL Query

Updating multiple rows in a PostgreSQL database using Go can be achieved efficiently with a single SQL statement. This approach offers a convenient way to update several records in one go, reducing the number of queries required and improving performance.

To perform bulk updates, we can utilize a derived table, which is a temporary table created within the scope of a query. This allows us to specify values for the rows to be updated. Here's how to do it:

UPDATE t
SET column_a = v.column_a,
    column_b = v.column_b
FROM (VALUES (1, 'FINISH', 1234),
             (2, 'UNFINISH', 3124)
     ) v(id, column_a, column_b)
WHERE v.id = t.id;

In this example, we create a derived table named v with three columns: id, column_a, and column_b. We provide values for two rows, which represent the updates we want to make. The WHERE clause matches the id column of the derived table with the id column of the table we're updating (t).

This approach makes it easy to add additional rows to update, simply by adding more rows to the derived table. This is less error-prone compared to writing multiple individual update statements.

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