Problem

You have a list of record IDs and their corresponding new values, and you need to update them all without writing individual UPDATE statements for each row.

Solution

Use a temporary table to hold your data, then UPDATE with a JOIN.

-- Create temporary table with your data
CREATE TEMPORARY TABLE temp_updates (
  id INT,
  new_value VARCHAR(255)
);

-- Insert your data
INSERT INTO temp_updates (id, new_value) VALUES
(123, 'Updated Value A'),
(456, 'Updated Value B'),
(789, 'Updated Value C');

-- Perform the bulk update
UPDATE target_table t
INNER JOIN temp_updates tu ON t.id = tu.id
SET t.some_column = tu.new_value;

-- Clean up
DROP TEMPORARY TABLE temp_updates;

Notes

• Works in MySQL and MariaDB
• For PostgreSQL, use UPDATE ... FROM syntax instead
• For large datasets (>10k rows), consider batching the updates
• Always test with a SELECT first: SELECT t.*, tu.* FROM target_table t INNER JOIN temp_updates tu ON t.id = tu.id

Variation: Update multiple columns

UPDATE target_table t
INNER JOIN temp_updates tu ON t.id = tu.id
SET 
  t.column_a = tu.new_value_a,
  t.column_b = tu.new_value_b,
  t.updated_at = NOW();