Scripts & Serendipity

How to Write a SQL UPSERT (Compared Across MySQL, PostgreSQL, and BigQuery) An "UPSERT" — update if a row exists, insert if it doesn't — is one of those operations every database supports, but with completely different syntax. If you've moved between databases, you've probably had to relearn this every time. Here's all three side by side. 📘 Why UPSERT Matters Without UPSERT, handling "insert or update" logic means checking for existence first, then branching — two round trips and a race condition risk under concurrent writes. UPSERT does it atomically, in one statement. 🧪 MySQL: INSERT ... ON DUPLICATE KEY UPDATE INSERT INTO inventory (product_id, quantity) VALUES (101, 50) ON DUPLICATE KEY UPDATE quantity = quantity + 50; Requires a UNIQUE or PRIMARY KEY constraint on product_id for MySQL to know what counts as a "duplicate." 🧪 PostgreSQL: INSERT ... ON CONFLICT DO UPDATE INSERT INTO inventory (product_id, quantity) VALUES ...

Avoiding Deadlocks in MySQL: A Practical Guide for Real-Time Systems If you've ever seen ERROR 1213 (40001): Deadlock found when trying to get lock in a production log at 2am, this post is for you. Deadlocks are one of those MySQL problems that seem rare until your system has real concurrent traffic — then they show up weekly. 📘 What Is a Deadlock? A deadlock happens when two (or more) transactions each hold a lock the other one needs, and neither can proceed. MySQL's InnoDB engine detects this automatically and kills one of the transactions (the "victim") to break the cycle — but that still means one of your transactions failed, and your application needs to handle that gracefully. 🧪 A Simple Example -- Transaction A START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- Transaction B (running at the same time) START TRANSACTION; UPDATE accounts SET balance = balanc...

MySQL MERGE Concept & Alternatives Explained MySQL MERGE Concept & Alternatives MySQL MERGE is not directly supported as a single SQL command like in some other database systems (e.g. SQL Server or Oracle), but the concept can be implemented using a combination of INSERT , UPDATE , and DELETE statements. The MERGE approach is helpful for syncing two tables where data may need to be inserted, updated, or removed depending on conditions. When implemented manually, this requires crafting multiple SQL queries to simulate the behavior. Users should have SELECT, INSERT, DELETE , and UPDATE privileges on both tables used in the MERGE operation. 🔧 Three Logical Cases in a MERGE Operation Case 1 – INSERT If a row exists in the source table but not in the target table, use INSERT to add it to the target. Case 2 – DELETE If a row exists in the tar...