MySQL: Insert a new row or update if it already exists

Other Questions MySQL
1

I want to insert a row into my database’s MySQL table, but I need it to update the current row if there’s already a row with the same unique key, instead of causing an error.

For instance:

INSERT INTO users (user_id, username, age) VALUES(5, "John", 25);

Here, user_id serves as the unique key. If there is a record in my database with user_id = 5, I would prefer it to update that record with the new username and age values rather than just erroring out. I’ve tried using INSERT IGNORE, but it skips the operation without making the necessary updates. What’s the right approach to achieve an insert or update action in MySQL?

2

you could also try REPLACE INTO. it deletes the existing row and inserts the new one:

REPLACE INTO users (user_id, username, age) VALUES(5, "John", 25);

just heads up - it’ll reset any columns you don’t specify back to their defaults.

3

You can achieve the desired behavior using the ON DUPLICATE KEY UPDATE clause with your SQL statement. Here’s how it works:

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

This will insert a new row with user_id 5 if it doesn’t exist, or update the username and age if it does. It’s an efficient way to handle insert and update operations in a single query.

4

Try using INSERT … ON DUPLICATE KEY UPDATE with explicit column references instead of VALUES(). Works way better in complex situations:

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

Instead of relying on VALUES(), you explicitly say what to update. Super helpful when you’re only updating certain columns or when your update values are different from insert values. I’ve hit cases where VALUES() acted weird with calculated fields or when mixing literals with column references.