Скачать или смотреть ⚡ SQL One-Liner: Upsert Records with a Single Statement!

Upserting—combining insert and update operations—is essential for synchronizing data without the overhead of separate logic. The traditional two-step approach uses a procedural check: you first UPDATE the target table, then check if any rows were affected and INSERT if not. This method works but involves multiple round-trips to the database and procedural control flow, which can diminish performance and complicate your codebase

Modern MySQL and MariaDB simplify upserts with the non‑standard but widely used INSERT … ON DUPLICATE KEY UPDATE syntax. By specifying the unique key conflict resolution inline, you can perform a safe upsert in a single atomic statement. The VALUES(col) function within the update clause directly references the intended insert values, making it easy to update multiple columns without repetition

This one-liner not only reduces code complexity but also improves performance by minimizing network chatter and locking effects

While this feature is not part of the SQL standard, it is supported by MySQL, MariaDB, and emulated in other systems via equivalent syntax (e.g., MERGE in SQL Server/PostgreSQL). Employing ON DUPLICATE KEY UPDATE is considered best practice for simple upsert operations when unique constraints are well-defined

Queries:

✅ Long Way (Separate INSERT and UPDATE):

-- 1. Try to UPDATE existing record
UPDATE users
SET name = 'Alice', email = '[email protected]'
WHERE id = 1;

-- 2. Check if the UPDATE matched any rows, then INSERT if not
IF ROW_COUNT() = 0 THEN
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[email protected]');
END IF;

Explanation:

This method first attempts to update the record with id = 1. If no rows are affected (ROW_COUNT() = 0), it proceeds to insert the new record. It requires procedural logic and multiple statements

✅ Shortcut One-Liner (MySQL/MariaDB ON DUPLICATE KEY UPDATE):

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[email protected]')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);

Explanation:

The ON DUPLICATE KEY UPDATE clause turns the INSERT into an “upsert”. If a unique key conflict occurs (here, on id), MySQL updates the existing row instead of throwing an error

The VALUES(col) function refers to the value that would have been inserted, enabling concise updates of multiple columns