Home >Database >Mysql Tutorial >How Can I Efficiently Update Multiple Rows Concurrently in PostgreSQL?

How Can I Efficiently Update Multiple Rows Concurrently in PostgreSQL?

Patricia Arquette
Patricia ArquetteOriginal
2025-01-12 22:56:49988browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn