How Can I Efficiently Update Multiple Rows Concurrently in PostgreSQL?

Optimizing PostgreSQL Multi-Row Updates

Efficiently updating numerous rows in PostgreSQL is crucial for database management. While individual UPDATE statements work, PostgreSQL offers more efficient methods for batch updates.

One approach uses the UPDATE ... SET syntax with multiple WHERE clauses:

<code class="language-sql">UPDATE table
SET
  column_a = 1 WHERE column_b = '123',
  column_a = 2 WHERE column_b = '345';</code>

However, this method lacks flexibility for complex updates across multiple columns.

A superior approach leverages the UPDATE ... FROM syntax with a values table:

<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 technique readily handles multi-column updates:

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

These advanced techniques significantly improve batch update performance in PostgreSQL, reducing execution time and simplifying database maintenance.

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