SQL UPSERT Function
Quick summary: UPSERT inserts a new row or updates an existing row if a conflict occurs (syntax depends on the database).
SQL UPSERT Syntax
INSERT INTO table_name (id, name) VALUES (1, 'name') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.nameSQL
SQL UPSERT Basic examples
-- PostgreSQL
INSERT INTO users (email, name)
VALUES ('a@test.com','Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;SQL
Output:
Row inserted or updatedInserts or updates on unique key conflict.
SQL UPSERT Real-world usage
-- MySQL
INSERT INTO users (email, name)
VALUES ('a@test.com','Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);SQL
Output:
Row inserted or updatedMySQL-style UPSERT using ON DUPLICATE KEY.
SQL UPSERT Edge cases
INSERT INTO users VALUES ();SQL
Output:
SQL errorUPSERT still requires valid data.
SQL UPSERT Common mistakes
Assuming UPSERT is standard SQL
Syntax differs between databases.
Incorrect
UPSERT INTO users ...Correct
INSERT ... ON CONFLICT / ON DUPLICATE KEYUse database-specific syntax.
SQL UPSERT Frequently Asked Questions
What is UPSERT in SQL?
Insert a row or update it if it already exists.
Use case of UPSERT?
Avoid duplicate records.
Syntax differs by DB?
Yes (ON CONFLICT, MERGE, ON DUPLICATE KEY).
Common mistake?
Wrong conflict target.
Performance?
Efficient with indexes.
Requires unique constraint?
Yes.
Safe?
Yes.
Used in APIs?
Yes.
Alternative?
Manual SELECT + INSERT/UPDATE.
Handles concurrency?
Yes with constraints.
Supported in MySQL?
Yes.
Best practice?
Use proper indexes.