INSERT, UPDATE, DELETE & Upsert (ON DUPLICATE KEY)

« SELECT, Filtering, Sorting & Pagination« Previous Table Relationships & JOINs (INNER, LEFT, RIGHT) »Next »

Perform atomic data modifications, learn the difference between DELETE and TRUNCATE, and handle upserts gracefully.

Atomic Upserts with ON DUPLICATE KEY UPDATE

When inserting a record that might already exist (based on a UNIQUE key), MySQL provides ON DUPLICATE KEY UPDATE to avoid throwing primary key collision errors.

Upserting User Visit Counters

Try It Yourself »

INSERT INTO UserStats (UserID, PageViews, LastVisit)
VALUES (101, 1, NOW())
ON DUPLICATE KEY UPDATE
    PageViews = PageViews + 1,
    LastVisit = NOW();

Warning / Pitfall

TRUNCATE TABLE drops and re-creates the entire table structure, resetting AUTO_INCREMENT counters to 1. It is DDL, cannot be rolled back in older MySQL versions, and bypasses row-level triggers!

Google AdSense Slot (inContentBanner)

Set your Publisher ID in src/config/ads.ts to display live ads.

Test Yourself With Exercise

+15 XP

What is a major operational difference between DELETE FROM table and TRUNCATE TABLE?

« Previous: SELECT, Filtering, Sorting & Pagination Next: Table Relationships & JOINs (INNER, LEFT, RIGHT) »