SQL

Perform Atomic Insert or Update (Upsert) in SQL

Efficiently insert a new record or update an existing one atomically using SQL's UPSERT pattern. Prevents race conditions and ensures data integrity.

INSERT INTO products (product_id, product_name, price)
VALUES (101, 'New Gadget', 29.99)
ON CONFLICT (product_id) DO UPDATE SET
    product_name = EXCLUDED.product_name,
    price = EXCLUDED.price;

How it works: This snippet illustrates an 'upsert' operation, atomically inserting a new record or updating an existing one if a conflict occurs. `ON CONFLICT (product_id)` specifies that if a row with the given `product_id` already exists, the `DO UPDATE SET` clause will execute. `EXCLUDED.product_name` and `EXCLUDED.price` refer to the values that would have been inserted. (Note: For MySQL, the syntax is `INSERT ... VALUES ... ON DUPLICATE KEY UPDATE column = VALUES(column);`)

Perform Atomic Insert or Update (Upsert) in SQL

Need help integrating this into your project?