I still remember the first time I ran an upsert against a live database and watched two problems disappear in one move.
Upsert
The word itself is a portmanteau, born from squeezing update and insert into a single command that a database engine understands as one job.
In plain terms, a database operation like this checks a table for a matching value and then decides on its own whether to touch an existing row or add a new row, without you writing separate logic for each row.
Picture a simple employees table with an id column acting as the unique constraint, sitting right next to columns for name and email. If the key value upsert you send already lives in that primary key, the engine treats it as an update existing row situation and overwrites what’s there.
If the value is new, it fires an insert new row action instead, and either way you never have to write the branching logic yourself.
Different systems speak this idea in their own dialects CockroachDB, for instance, has direct UPSERT syntax built into its SQL statement grammar, which is rare among RDBMS platforms.
What is an upsert in SQL?
Most relational database engines don’t even use the word “upsert” anywhere in their manuals, which is exactly why so many developers go hunting for it and find nothing.
Under the hood, though, the goal stays the same: avoid a duplicate key clash and skip the messy race condition that comes from treating this as two separate steps instead of one atomic statement.
How do you perform an upsert in MySQL?
MySQL never adopted a dedicated UPSERT command, so anyone coming from a database that has one needs to learn a different piece of syntax.
The real workhorse here is INSERT ON DUPLICATE KEY UPDATE, a mouthful of a clause that still does exactly what its name promises. Send it a duplicate value for a key column, and instead of throwing an error, the database quietly upsert switches from inserting to updating.
What actually triggers this switch is a unique key violation MySQL doesn’t check just the primary key column by name, it reacts to any unique key collision it finds. When that collision happens, MySQL runs an update existing row action on the spot rather than rejecting the write outright.
When there’s no collision, it simply completes an insert new row action, and you get the same one-statement convenience that other databases achieve through their own duplicate key handling.
Why not just insert or update?
Before upserts existed, I used to write the manual approach myself: check whether a row is there, then decide whether to insert or update. That check-then-insert pattern needs two round trips to the database, and it hides a nasty trap most people don’t upsert notice until production breaks.
Another process can slip in and add the same row between your check and your write, and suddenly your insert collides with a primary key or unique constraint you thought was safe.
That collision shows up as a duplicate key failure, and it only happens because the check and the write were never truly one action.
An upsert removes that gap entirely by wrapping everything into a single atomic statement, so the database itself owns the conflict resolution instead of your application code.
There’s no window left for a race condition to sneak upsert through, because the whole operation either succeeds as one unit or doesn’t happen at all.
The syntax depends on your database
Every database dialect handles this idea in its own way, and I’ve had to relearn the syntax more than once switching between projects. PostgreSQL and SQLite both lean on INSERT ON CONFLICT, a clause that lets you follow up with DO UPDATE SET to overwrite fields or fall back to DO NOTHING and skip the row quietly.
Inside that clause, the special keyword EXCLUDED refers to whatever values you tried to insert in the first place, which comes in handy once you’re comfortable with the pattern.
MySQL, on the other hand, reacts to any unique upsert key violation rather than naming specific columns, which we already covered above.
The SQL standard takes yet another route through MERGE, supported by Oracle, SQL Server, Snowflake, and BigQuery, where you compare a source table against a target table and spell out behavior for WHEN MATCHED and WHEN NOT MATCHED cases.
This style shows up constantly in data warehouse load jobs, because MERGE can insert, update, and delete rows in one single pass, and that flexibility matters when you’re loading millions of records overnight.
Why upserts matter for analytics?
Nobody types an upsert straight into a BI tool tools like Metabase exist purely to read data, not write it, so the writing happens further upstream.
That upstream work usually comes from ETL jobs or sync tools feeding a data pipeline, and their whole job is to run idempotently, meaning the same sync can run twice without creating a mess.
An idempotent load keeps every entity appearing once in a transactional database or warehouse table, always reflecting its latest state instead of a pile of old copies.
I’ve personally traced more than one broken dashboard back to this exact gap, where duplicate customers or double-counted orders were quietly inflating the numbers in analytics reports.
Nine times out of ten, the root cause was a missing upsert somewhere in the pipeline, or a unique key that nobody had set up on the target table.
Fixing that one step upstream almost always fixes the dashboard downstream, which is why I always check the load logic first before blaming upsert the analytics tool.
FAQS About Upsert
What does upsert mean?
An upsert is a database operation that decides whether to run an update or an insert for a row.
It checks for a matching value in a table and acts on its own, so you don’t write that logic yourself.
What’s the difference between update and upsert?
An update only touches a row that already exists, and does nothing if the key value is missing.
An upsert handles both cases — it updates the existing row on a match, or adds a new row if there isn’t one.
What is the difference between upsert and insert operations?
A plain insert fails when it hits a duplicate key, since it assumes every row is new.
An upsert checks first and runs an update existing row action instead, avoiding that duplicate key failure.
What is upsert in ETL?
In an ETL or data pipeline, an upsert keeps a warehouse table clean by loading data idempotently.
This stops sync tools from creating duplicate customers or double-counted orders on the dashboard.
I still remember the first time I ran an upsert against a live database and watched two problems disappear in one move.
Upsert
The word itself is a portmanteau, born from squeezing update and insert into a single command that a database engine understands as one job.
In plain terms, a database operation like this checks a table for a matching value and then decides on its own whether to touch an existing row or add a new row, without you writing separate logic for each row.
Picture a simple employees table with an id column acting as the unique constraint, sitting right next to columns for name and email. If the key value upsert you send already lives in that primary key, the engine treats it as an update existing row situation and overwrites what’s there.
If the value is new, it fires an insert new row action instead, and either way you never have to write the branching logic yourself.
Different systems speak this idea in their own dialects CockroachDB, for instance, has direct UPSERT syntax built into its SQL statement grammar, which is rare among RDBMS platforms.
What is an upsert in SQL?
Most relational database engines don’t even use the word “upsert” anywhere in their manuals, which is exactly why so many developers go hunting for it and find nothing.
Under the hood, though, the goal stays the same: avoid a duplicate key clash and skip the messy race condition that comes from treating this as two separate steps instead of one atomic statement.
How do you perform an upsert in MySQL?
MySQL never adopted a dedicated UPSERT command, so anyone coming from a database that has one needs to learn a different piece of syntax.
The real workhorse here is INSERT ON DUPLICATE KEY UPDATE, a mouthful of a clause that still does exactly what its name promises. Send it a duplicate value for a key column, and instead of throwing an error, the database quietly upsert switches from inserting to updating.
What actually triggers this switch is a unique key violation MySQL doesn’t check just the primary key column by name, it reacts to any unique key collision it finds. When that collision happens, MySQL runs an update existing row action on the spot rather than rejecting the write outright.
When there’s no collision, it simply completes an insert new row action, and you get the same one-statement convenience that other databases achieve through their own duplicate key handling.
Why not just insert or update?
Before upserts existed, I used to write the manual approach myself: check whether a row is there, then decide whether to insert or update. That check-then-insert pattern needs two round trips to the database, and it hides a nasty trap most people don’t upsert notice until production breaks.
Another process can slip in and add the same row between your check and your write, and suddenly your insert collides with a primary key or unique constraint you thought was safe.
That collision shows up as a duplicate key failure, and it only happens because the check and the write were never truly one action.
An upsert removes that gap entirely by wrapping everything into a single atomic statement, so the database itself owns the conflict resolution instead of your application code.
There’s no window left for a race condition to sneak upsert through, because the whole operation either succeeds as one unit or doesn’t happen at all.
The syntax depends on your database
Every database dialect handles this idea in its own way, and I’ve had to relearn the syntax more than once switching between projects. PostgreSQL and SQLite both lean on INSERT ON CONFLICT, a clause that lets you follow up with DO UPDATE SET to overwrite fields or fall back to DO NOTHING and skip the row quietly.
Inside that clause, the special keyword EXCLUDED refers to whatever values you tried to insert in the first place, which comes in handy once you’re comfortable with the pattern.
MySQL, on the other hand, reacts to any unique upsert key violation rather than naming specific columns, which we already covered above.
The SQL standard takes yet another route through MERGE, supported by Oracle, SQL Server, Snowflake, and BigQuery, where you compare a source table against a target table and spell out behavior for WHEN MATCHED and WHEN NOT MATCHED cases.
This style shows up constantly in data warehouse load jobs, because MERGE can insert, update, and delete rows in one single pass, and that flexibility matters when you’re loading millions of records overnight.
Why upserts matter for analytics?
Nobody types an upsert straight into a BI tool tools like Metabase exist purely to read data, not write it, so the writing happens further upstream.
That upstream work usually comes from ETL jobs or sync tools feeding a data pipeline, and their whole job is to run idempotently, meaning the same sync can run twice without creating a mess.
An idempotent load keeps every entity appearing once in a transactional database or warehouse table, always reflecting its latest state instead of a pile of old copies.
I’ve personally traced more than one broken dashboard back to this exact gap, where duplicate customers or double-counted orders were quietly inflating the numbers in analytics reports.
Nine times out of ten, the root cause was a missing upsert somewhere in the pipeline, or a unique key that nobody had set up on the target table.
Fixing that one step upstream almost always fixes the dashboard downstream, which is why I always check the load logic first before blaming upsert the analytics tool.
FAQS About Upsert
What does upsert mean?
An upsert is a database operation that decides whether to run an update or an insert for a row.
It checks for a matching value in a table and acts on its own, so you don’t write that logic yourself.
What’s the difference between update and upsert?
An update only touches a row that already exists, and does nothing if the key value is missing.
An upsert handles both cases — it updates the existing row on a match, or adds a new row if there isn’t one.
What is the difference between upsert and insert operations?
A plain insert fails when it hits a duplicate key, since it assumes every row is new.
An upsert checks first and runs an update existing row action instead, avoiding that duplicate key failure.
What is upsert in ETL?
In an ETL or data pipeline, an upsert keeps a warehouse table clean by loading data idempotently.
This stops sync tools from creating duplicate customers or double-counted orders on the dashboard.