{"id":10822,"date":"2023-09-26T22:11:30","date_gmt":"2023-09-26T20:11:30","guid":{"rendered":"https:\/\/monodes.com\/predaelli\/?p=10822"},"modified":"2023-09-26T22:11:33","modified_gmt":"2023-09-26T20:11:33","slug":"upsert-in-sql","status":"publish","type":"post","link":"https:\/\/monodes.com\/predaelli\/2023\/09\/26\/upsert-in-sql\/","title":{"rendered":"Upsert in SQL"},"content":{"rendered":"\n
\n

Inserting and updating data with a single query.<\/p>\nSource: Upsert in SQL<\/a><\/em><\/cite><\/blockquote>\n\n\n\n\n\n\n\n

\n

Upsert in SQL<\/h1>\n\n\n\n

Upsert<\/em> is an operation that \u278a inserts new records into the database and \u278b updates existing ones. Let’s see how it works in different DBMS. The examples are interactive, so you can read and practice.<\/p>\n\n\n\n

We will use the toy employees<\/code> table:<\/p>\n\n\n\n

\u250c\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 id \u2502 name  \u2502  city  \u2502 department \u2502 salary \u2502\n\u251c\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 11 \u2502 Diane \u2502 London \u2502 hr         \u2502 70     \u2502\n\u2502 12 \u2502 Bob   \u2502 London \u2502 hr         \u2502 78     \u2502\n\u2502 21 \u2502 Emma  \u2502 London \u2502 it         \u2502 84     \u2502\n\u2502 22 \u2502 Grace \u2502 Berlin \u2502 it         \u2502 90     \u2502\n\u2502 23 \u2502 Henry \u2502 London \u2502 it         \u2502 104    \u2502\n\u2502 24 \u2502 Irene \u2502 Berlin \u2502 it         \u2502 104    \u2502\n\u2502 31 \u2502 Cindy \u2502 Berlin \u2502 sales      \u2502 96     \u2502\n\u2502 32 \u2502 Dave  \u2502 London \u2502 sales      \u2502 96     \u2502\n\u2514\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n<\/code><\/pre>\n\n\n\n
Let’s say we are adding two new employees:<\/p>\n\n\n\n
(25, 'Frank', 'Berlin', 'it', 120)\n(33, 'Alice', 'Berlin', 'sales', 100)\n<\/code><\/pre>\n\n\n\n
Hello, Frank. Hello, Alice.<\/p>\n\n\n\n
And updating the other two:<\/p>\n\n\n\n
- (11, 'Diane', 'London', 'hr', 70)\n+ (11, 'Diane', 'Berlin', 'hr', 70)\n\n- (21, 'Emma', 'London', 'it', 84)\n+ (21, 'Emma', 'London', 'it', 95)\n<\/code><\/pre>\n\n\n\n
Diane is moving to Berlin, while Emma’s salary is increasing to 95.<\/p>\n\n\n\n
You can read this article from start to finish, or you can skip to a section about the specific database engine:<\/p>\n\n\n\n
MySQL\/MariaDB<\/a> \u2022 SQLite<\/a> \u2022 PostgreSQL<\/a> \u2022 SQL Server<\/a> \u2022 Oracle<\/a> \u2022 Others<\/a><\/p>\n\n\n\n
INSERT<\/h2>\n\n\n\n
Let’s try the easy way:<\/p>\n\n\n\n
insert into employees\n(id, name, city, department, salary)\nvalues\n(11, 'Diane', 'Berlin', 'hr', 70),\n(21, 'Emma', 'London', 'it', 95),\n(25, 'Frank', 'Berlin', 'it', 120),\n(33, 'Alice', 'Berlin', 'sales', 100);\n<\/code><\/pre>\n\n\n\n
Edit<\/a><\/p>\n\n\n\n
The first table is “before insert”, the second is “after insert”. We’ll use this notation for all examples.<\/p>\n\n\n\n
As expected, it fails for Diane and Emma: the employee ID is a primary key, so we can’t have two employees with the same ID. We’ll have to try something else.<\/p>\n\n\n\n
MySQL: REPLACE<\/h2>\n\n\n\n
MySQL’s replace into<\/code> inserts records with new primary\/unique keys (in our case, employee IDs) and replaces the ones with existing keys:<\/p>\n\n\n\n
replace into employees\n(id, name, city, department, salary)\nvalues\n(11, 'Diane', 'Berlin', 'hr', 70),\n(21, 'Emma', 'London', 'it', 95),\n(25, 'Frank', 'Berlin', 'it', 120),\n(33, 'Alice', 'Berlin', 'sales', 100);\n<\/code><\/pre>\n\n\n\n
Edit<\/a><\/p>\n\n\n\n
Added Frank and Alice, replaced Diane and Emma.<\/p>\n\n\n\n
The bad thing about replace<\/code> is that it does an actual delete<\/code> followed by insert<\/code>, which has some unexpected side effects<\/a> like firing on delete<\/code> triggers.<\/p>\n\n\n\n
Also, you can’t reference the old values when replacing the record. For example, you can’t set city<\/code> = Berlin (prev. London)<\/code> for Diane.<\/p>\n\n\n\n
\u2192 Documentation<\/a><\/p>\n\n\n\n
To ignore the duplicates and only perform inserts, you can use insert ignore<\/code>:<\/p>\n\n\n\n
insert ignore into employees\n(id, name, city, department, salary)\nvalues\n(11, 'Diane', 'Berlin', 'hr', 70),\n(21, 'Emma', 'London', 'it', 95),\n(25, 'Frank', 'Berlin', 'it', 120),\n(33, 'Alice', 'Berlin', 'sales', 100);\n<\/code><\/pre>\n\n\n\n
Edit<\/a><\/p>\n\n\n\n
Added Frank and Alice, ignored Diane and Emma.<\/p>\n\n\n\n
\u2192 Documentation<\/a><\/p>\n\n\n\n
replace<\/code> is probably not the best way to do upserts in MySQL. There is a better alternative \u2193<\/p>\n\n\n\n
MySQL: INSERT…ON DUPLICATE KEY UPDATE<\/h2>\n\n\n\n
MySQL’s on duplicate key update<\/code> inserts records with new primary\/unique keys (in our case, employee IDs) and updates the ones with existing keys:<\/p>\n\n\n\n
insert into employees\n(id, name, city, department, salary)\nvalues\n  (11, 'Diane', 'Berlin', 'hr', 70),\n  (21, 'Emma', 'London', 'it', 95),\n  (25, 'Frank', 'Berlin', 'it', 120),\n  (33, 'Alice', 'Berlin', 'sales', 100)\non duplicate key update\n  city = values(city),\n  salary = values(salary);\n<\/code><\/pre>\n\n\n\n
Edit<\/a><\/p>\n\n\n\n
Added Frank and Alice, updated Diane and Emma.<\/p>\n\n\n\n
When updating a record, you can reference the old value by column name (city<\/code>) and the new value with the values()<\/code> function (values(city)<\/code>). Now we can actually update a value, not just replace it:<\/p>\n\n\n\n
insert into employees\n(id, name, city, department, salary)\nvalues\n  (11, 'Diane', 'Berlin', 'hr', 70)\non duplicate key update\n  city = concat(values(city), ' (prev. ', city, ')');\n<\/code><\/pre>\n\n\n\n
Edit<\/a><\/p>\n\n\n\n
Updated the city for Diane and kept the old value for reference.<\/p>\n\n\n\n
Starting with MySQL 8.0, you can alias the new values and not use the ugly values()<\/code> function:<\/p>\n\n\n\n
insert into employees\n(id, name, city, department, salary)\nvalues\n  (11, 'Diane', 'Berlin', 'hr', 70),\n  (21, 'Emma', 'London', 'it', 95),\n  (25, 'Frank', 'Berlin', 'it', 120),\n  (33, 'Alice', 'Berlin', 'sales', 100)\n  as new\non duplicate key update\n  city = new.city,\n  salary = new.salary;\n<\/code><\/pre>\n\n\n\n
Edit<\/a><\/p>\n\n\n\n
Using the new<\/code> alias for new records.<\/p>\n\n\n\n
\u2192 Documentation<\/a><\/p>\n\n\n\n
SQLite: INSERT OR…<\/h2>\n\n\n\n
SQLite’s insert or replace<\/code> inserts records with new primary\/unique keys (in our case, employee IDs) and replaces the ones with existing keys:<\/p>\n\n\n\n
insert or replace into employees\n(id, name, city, department, salary)\nvalues\n(25, 'Frank', 'Berlin', 'it', 120),\n(33, 'Alice', 'Berlin', 'sales', 100),\n(11, 'Diane', 'Berlin', 'hr', 70),\n(21, 'Emma', 'London', 'it', 95);\n<\/code><\/pre>\n\n\n\n
Edit<\/a><\/p>\n\n\n\n
Added Frank and Alice, replaced Diane and Emma.<\/p>\n\n\n\n
Like MySQL’s replace<\/code>, SQLite’s or replace<\/code> deletes existing duplicate records before inserting the new ones. It fires delete triggers in some cases and does not allow references to old values.<\/p>\n\n\n\n
SQLite provides a number of alternative strategies instead of replace<\/code>:<\/p>\n\n\n\n