MySQL upsert operation - add new record or modify existing one

Other Questions MySQL
1

I need to perform an upsert operation in MySQL. When I try to insert a new record, I want it to either create a new row if it doesn’t exist, or update the existing row if there’s already a record with the same primary key.

Here’s what I’m working with:

INSERT INTO users (user_id, username, years_old) VALUES(5, "john_doe", 25);

The primary key in my table is user_id. If there’s already a record where user_id = 5, I want to update that existing record with the new username and age values instead of getting a duplicate key error.

I tried using INSERT IGNORE but that just skips the insertion without updating the existing data. What’s the proper way to handle this scenario in MySQL?

2

Both approaches have different performance trade-offs. ON DUPLICATE KEY UPDATE usually wins when you’re mostly updating existing records - it skips the delete-insert overhead that REPLACE creates. But with high concurrency, you might hit locking issues that REPLACE doesn’t have. Check your MySQL version too since duplicate handling syntax has changed a lot. Pre-5.7 versions had quirky VALUES() function behavior that could bite you in edge cases.

3

The ON DUPLICATE KEY UPDATE clause is exactly what you need. Here’s how it looks:

INSERT INTO users (user_id, username, years_old) 
VALUES(5, "john_doe", 25)
ON DUPLICATE KEY UPDATE 
    username = VALUES(username),
    years_old = VALUES(years_old);

I’ve used this tons in production and it’s rock solid. The VALUES() function grabs the values from your INSERT statement. If user_id 5 exists, it updates username and years_old with your new values. If it doesn’t exist, it just does a regular insert. Just make sure you’ve got a primary key or unique constraint on user_id or this won’t work.

4

You can also use REPLACE INTO for complete row replacements:

REPLACE INTO users (user_id, username, years_old) VALUES(5, "john_doe", 25);

I like this when I need to completely overwrite an existing record instead of updating specific columns. Just heads up though - REPLACE actually deletes the old row and creates a new one behind the scenes. Any columns you don’t specify will reset to their default values. This’ll bite you if your table has other columns with important data you want to keep. For partial updates, stick with the ON DUPLICATE KEY UPDATE method from above.

5

mySQL 8.0+ gives you the INSERT ... ON CONFLICT syntax, but many use ON DUPLICATE KEY UPDATE for compatibility with older versions. make sure your user_id has a unique key, or these upsert options won’t work!