Mysql ON DUPLICATE KEY UPDATE for multiple rows insert in single query

sql

bulk-insertions

sql-injection

decimal-values

byNikita Barsukov·Dec 16, 2024

To perform a bulk **INSERT** with **ON DUPLICATE KEY UPDATE**, list the new values and define the update rule per column. This way, existing rows will be modified with the new data in case of key conflicts:

INSERT INTO table_name (col1, col2) VALUES
  (val1_1, val1_2),
  (val2_1, val2_2),
  ...
ON DUPLICATE KEY UPDATE 
  col1 = VALUES(col1), 
  col2 = VALUES(col2);

This means for each duplicate key encountered, col1 and col2 will be updated based on the corresponding values in the VALUES list.

The Power of ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE is highly useful during bulk insertions. You combine the power of INSERT and UPDATE in one neat package. It lowers database load, reduces network latency and simplifies your application layer.

# SQL's version of a 2-for-1 deal🎉
INSERT INTO table_name (col1, col2) VALUES 
  (val1_1, val1_2),
  (val2_1, val2_2),
  ...
ON DUPLICATE KEY UPDATE 
  col1 = VALUES(col1), 
  col2 = VALUES(col2);

Starting from MySQL 8.0.19, you'd get to use the VALUE keyword instead of VALUES(col1) which enhances your code readability. The VALUE keyword points out the new values you intend to update, a true example of reading code like a book.

Non-primary unique keys require some attention, grasp the unique constraints and avoid unexpected data overrides. Bulk operations will get a substantial efficiency boost thanks to this MySQL feature.

# It's the UPSERT time 🚀
INSERT INTO table_name (id, col1, col2) VALUES 
  (1, 'A', 'X'),
  (2, 'B', 'Y'),
  ...
ON DUPLICATE KEY UPDATE 
  col1 = VALUES(col1), 
  col2 = VALUES(col2);

SQL Server Alternative Approach

On SQL Server, a temporary table with the MERGE statement will give you a similar effect. The MERGE statement offers when matched then update and when not matched then insert logic.

# Here's how SQL Server says "Hi, MySQL! 👋"
MERGE INTO target_table AS t
USING source_table AS s
ON t.key_col = s.key_col
WHEN MATCHED THEN 
  UPDATE SET t.col1 = s.col1, t.col2 = s.col2
WHEN NOT MATCHED BY TARGET THEN
  INSERT (key_col, col1, col2)
  VALUES (s.key_col, s.col1, s.col2);

Boosting Performance While Ensuring Security

Protect against SQL injection by using placeholders (?) and passing values as arrays. This ensures robust security while performing your bulk data operations.

In a JavaScript environment, using async/await, coupled with Promises, means efficient asynchronous database operations and robust error handling. Arrow functions are another way to make your code more readable.

Keeping Up with Decimal Values

To ensure accurate handling of decimal values, utilize the backtick symbol (`). This preserves the true decimal points during database transactions:

# Keeping your 💰 to the exact penny
INSERT INTO financials (account, amount) VALUES
  (`123456`, `25.75`),
  (`654321`, `100.50`),
  ...
ON DUPLICATE KEY UPDATE 
  amount = VALUES(amount);