SQL
Perform Upsert Operations (Insert or Update on Conflict)
Master SQL upsert operations using ON CONFLICT (PostgreSQL/SQLite) or ON DUPLICATE KEY UPDATE (MySQL) to insert new records or update existing ones based on a unique key, preventing duplicates.
INSERT INTO users (id, username, email) VALUES (1, 'john_doe', '[email protected]')
ON CONFLICT (id) DO UPDATE SET username = EXCLUDED.username, email = EXCLUDED.email;
How it works: This SQL snippet performs an 'upsert' operation. If a row with the specified `id` already exists (due to a unique constraint), it updates the `username` and `email` columns for that row using the values provided in the `INSERT` statement (accessed via `EXCLUDED`). If no row with the `id` exists, a new row is inserted. This is crucial for managing data where uniqueness must be maintained without explicit checks.