Update multiple rows in same query using PostgreSQL

sql

update

postgresql

performance

byAlex Kataev·Sep 7, 2024

For an efficient and targeted update of multiple rows, each with different values, use CASE within an UPDATE statement in PostgreSQL:

UPDATE your_table
SET column = CASE id
  WHEN 1 THEN 'valueA' -- First ID, first value
  WHEN 2 THEN 'valueB' -- Second ID, second value
  -- It's like pizza toppings, add as many as you want!
END
WHERE id IN (1, 2); -- Don't forget to specify the IDs you're updating!

Here, in one single query, you're hitting multiple targets. Bullseye! 🎯

The mighty power of `UPDATE...FROM` with a `VALUES` mapping

Here's an enhanced, scalable solution, using UPDATE...FROM and a VALUES mapping table:

UPDATE your_table t
SET
  column1 = v.column1,
  column2 = v.column2
FROM (VALUES
  (1, 'new_value1', 'new_valueA'), -- "Hey ID 1, we've got new values for you!"
  (2, 'new_value2', 'new_valueB') -- "Hey ID 2, your turn!"
  -- Include as many tuples as you have IDs. It's a party!
) AS v(id, column1, column2)
WHERE t.id = v.id; -- "ID, meet your new values. New values, meet your ID."

Like a Swiss army knife, this tool is versatile and ready for action in diverse scenarios.

Using `CASE` for versatile column updates

Channel the power of CASE for conditional updates across multiple columns in a single shot:

UPDATE your_table
SET
  column1 = CASE WHEN condition1 THEN 'result1' ELSE column1 END, -- "If you win the first condition, here's your prize!"
  column2 = CASE WHEN condition2 THEN 'result2' ELSE column2 END -- "If you win the second condition, here's YOUR prize!"
WHERE id IN (SELECT id FROM your_table WHERE condition_to_select_rows);

By doing this, you're becoming a SQL wizard, casting spells to transform your dataset.

Efficient updates with arrays using `unnest`

When you're handling updates for mammoth datasets, use unnest to make your update synchronized and efficient:

UPDATE your_table t
SET
  column1 = v.column1,
  column2 = v.column2
FROM (SELECT unnest(ARRAY[1,2]) AS id, unnest(ARRAY['new_value1','new_value2']) AS column1, unnest(ARRAY['new_valueA','new_valueB']) AS column2) v
WHERE t.id = v.id;

Think of unnest as a SQL DJ, syncing the beats (IDs and values) to create a harmonious mix.

Reliable and secure updates

Ensure reliability and security in your updates. Use parameterized queries with bind variables to shield against SQL injection and protect data integrity. Also, maintain your timestamps fresh by including updated_at=now() in the SET section.

Interactive practice with SQL Fiddle

Use SQL Fiddle to practice and visualize complex update queries safely before applying to a live system. Consider it your personal SQL Gym where you work out those pesky update queries!

Troubleshooting common pitfalls

Heads up for these potential issues:

Alignment Error: Align VALUES table columns with the target table's columns like aligned gears in a machine.
Identifier Mismatch: Use unique identifiers (id) for precise row assignment.
Inaccurate Updates: Incorrect identifiers in WHERE...IN can wreak havoc, leading to incorrect or missed updates.