Update multiple rows with different values in one query in MySQL

Getting savvy with CASE in UPDATE

Setting up complex conditions

If you're dealing with convoluted scenarios, combine CASE with additional clauses to refine your updates:

UPDATE your_table 
SET your_column = CASE 
  WHEN condition1 THEN 'valueA'  -- condition1, 'valueA', sitting in a tree
  WHEN condition2 THEN 'valueB'  -- condition2, 'valueB', as happy as can be
  ELSE your_column               -- don't swing if the pitch ain't right
END
WHERE another_condition;         -- add some spice to filter your life


Ensure that your conditions specifically target the correct rows to protect data integrity and adhere to your established business logic.

Pairing JOIN with case statement

To update based on data in another table, implement a JOIN:

UPDATE your_table
JOIN other_table ON your_table.foreign_key = other_table.primary_key
SET your_table.your_column = CASE 
  WHEN other_table.condition THEN 'new_value'  -- seek and you shall find 'new_value'
  ELSE your_table.your_column                  -- in case of lack of match, break ELSE
END
WHERE your_table.condition;                    -- another layer of exclusive clubbing


Temporary tables – a doorway to complicated updates

Create a temporary table if you're dealing with complex logic scenarios, then JOIN it during the update:

CREATE TEMPORARY TABLE temp_table AS 
SELECT id, new_value FROM calculation;  -- enlisting for the operation

UPDATE your_table
JOIN temp_table ON your_table.id = temp_table.id
SET your_table.your_column = temp_table.new_value;  -- mission accomplished

DROP TEMPORARY TABLE temp_table;  -- clean after yourself, pack out what you pack in


Exploiting alternative methods for slick updates

INSERT INTO ... ON DUPLICATE KEY UPDATE

When juggling with upserts (update or insert), consider this approach:

INSERT INTO your_table (id, your_column)
VALUES 
  (1, 'valueA'),  -- if 1 ain't there, bring 'valueA' to the party
  (2, 'valueB'),  -- if 2 is missing, 'valueB' will fill the void
  (3, 'valueC')   -- 'valueC' is an ace up your sleeve for missing 3
ON DUPLICATE KEY UPDATE 
  your_column = VALUES(your_column);  -- in case of déjà vu, let's get an update


Handling dates like a pro

Use native date types to store your dates, allowing for accurate comparison and storage:

UPDATE your_table
SET date_column = CASE
  WHEN id=1 THEN '2023-01-01'  -- id 1 loves a great start to the year
  WHEN id=2 THEN '2023-02-01'  -- id 2 goes for the month of love
  END
WHERE id IN (1,2);


Nuanced considerations for updating with non-unique keys

Navigating non-unique update keys

Maintain alertness when dealing with non-unique keys to avoid unexpected updates. The key column used in the WHERE clause should uniquely identify rows.

Striving for readability and maintainability

Exert effort to maintain readability and maintainability regardless of the complexity of updates. Comments, aliases, and formatting can enhance clarity.

Prioritizing performance

In case of large datasets, optimize your statements. Inefficient queries may burden your DB. Always evaluate the performance impact before and after optimizations.