INSERT ON DUPLICATE KEY UPDATE in SQL

Level 5281 keystrokes

INSERT ... ON DUPLICATE KEY UPDATE inserts a new row, or updates the existing one when a unique key would be duplicated (MySQL 8.0.20+ prefers a row alias over VALUES()).

SQL code

-- Try to insert one row of daily stats
1INSERT INTO daily_stats (
2 date,
3 page_views,
4 unique_visitors,
5 total_sales
6)
-- The values for the new row, in column order
7VALUES (
8 '2024-03-15',
9 1500,
10 800,
11 25000.00
12)
-- If date already exists (it must be a PRIMARY or UNIQUE key), update that row instead
13ON DUPLICATE KEY UPDATE
-- Add the new views to the stored count; VALUES(col) is the value we tried to insert
14 page_views = page_views + VALUES(page_views),
-- Replace the visitor count instead of adding to it
15 unique_visitors = VALUES(unique_visitors),
-- Add to total sales; "2 rows affected" means an existing row was updated
16 total_sales = total_sales + VALUES(total_sales);

Output

Query OK, 2 rows affected
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 12
  • ,× 8
  • (× 5
  • )× 5
  • =× 3
  • -× 2
  • '× 2
  • +× 2
  • ;× 1
  • .× 1

More SQL examples at level 5

← SQL typing practice