UPSERT in MySQL, PostgreSQL, SQLite & MS SQL Server — A Complete Comparison

One of the most searched SQL topics — and one of the most
confusing — is UPSERT: insert a row if it doesn't exist,
update it if it does.

The problem? Every database does it differently.

Here's the definitive comparison across all 4 major databases.

---

MySQL

INSERT INTO users (id, email) VALUES (1, '[e-mail]')
ON DUPLICATE KEY UPDATE email = VALUES(email);

Works on any duplicate key — primary or unique index.

---

PostgreSQL

INSERT INTO users (id, email) VALUES (1, '[e-mail]')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

EXCLUDED refers to the row that failed to insert.
You can also use ON CONFLICT DO NOTHING to silently skip.

---

SQLite

INSERT OR REPLACE INTO users (id, email)
VALUES (1, '[e-mail]');

SQLite deletes the old row and inserts a new one —
so auto-increment IDs will change. Watch out for that.

---

MS SQL Server

MERGE users AS target
USING (VALUES (1,'[e-mail]')) AS src(id,email)
ON target.id = src.id
WHEN MATCHED THEN
UPDATE SET email = src.email
WHEN NOT MATCHED THEN
INSERT (id,email) VALUES (src.id,src.email);

Most verbose — but also the most flexible.
You can add WHEN NOT MATCHED BY SOURCE THEN DELETE
to turn it into a full sync operation.

---

Quick Reference Table

Database	Syntax Style	Deletes old row?	Notes
MySQL	ON DUPLICATE KEY	No	Any unique key triggers
PostgreSQL	ON CONFLICT	No	Column-specific control
SQLite	INSERT OR REPLACE	Yes	ID changes on replace
SQL Server	MERGE	No	Most flexible syntax

---

Bonus: Python Script to Run UPSERT Across Any DB

If you're automating data pipelines, here's a Python
snippet that detects your DB type and runs the right
UPSERT automatically:

def upsert(conn, db_type, table, data, conflict_col):
    if db_type == "mysql":
        cols = ", ".join(data.keys())
        vals = ", ".join(["%s"] * len(data))
        updates = ", ".join([f"{k}=VALUES({k})" for k in data])
        sql = f"""INSERT INTO {table} ({cols}) VALUES ({vals})
                  ON DUPLICATE KEY UPDATE {updates}"""
    elif db_type == "postgresql":
        cols = ", ".join(data.keys())
        vals = ", ".join(["%s"] * len(data))
        updates = ", ".join([f"{k}=EXCLUDED.{k}" for k in data])
        sql = f"""INSERT INTO {table} ({cols}) VALUES ({vals})
                  ON CONFLICT ({conflict_col}) DO UPDATE SET {updates}"""
    elif db_type == "sqlite":
        cols = ", ".join(data.keys())
        vals = ", ".join(["?"] * len(data))
        sql = f"INSERT OR REPLACE INTO {table} ({cols}) VALUES ({vals})"

    cursor = conn.cursor()
    cursor.execute(sql, list(data.values()))
    conn.commit()

---

Want the Full Reference?

I put together a complete SQL cheat pack covering 50+
queries across all 4 databases — plus 5 Python automation
scripts for common DB tasks like exporting to CSV, finding
duplicates, and auto-documenting your schema.

It's on Gumroad here: https://bit.ly/4mIC6It

Happy to answer any SQL questions in the comments!