How to insert into a table or update if exists in MySQL?

Problem

You need to calculate a running total in SQL Server and provide a solution that is also reproducible in MySQL.

Input

employee_id	first_name	last_name	salary
1	John	Doe	50000.00
2	Jane	Smith	60000.00
3	Bob	Johnson	55000.00

Try Hands-On: Fiddle

Desired Output

On inserting a new row, if the entry already exists, it should update the existing entry.

For example, we want to update the employee_id = 1’s salary to 55000.

employee_id	first_name	last_name	salary
1	John	Doe	55000.00
2	Jane	Smith	60000.00
3	Bob	Johnson	55000.00

Solution 1:

You can use the INSERT INTO … ON DUPLICATE KEY UPDATE statement in MySQL to achieve this.

This statement will insert a new row into the table, or if a duplicate key violation occurs (i.e., a row with the same primary or unique key exists), it will update the existing row with the new values. Here’s the SQL query:

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES
    (1, 'John', 'Doe', 55000.00)
ON DUPLICATE KEY UPDATE
    first_name = VALUES(first_name),
    last_name = VALUES(last_name),
    salary = VALUES(salary);

Explanation:

In this example, we’re trying to insert a row with employee_id 1, which already exists in the table.

Instead of inserting a new row, this query will update the existing row with the new salary value (55000.00).

You can execute this query to insert or update data into the employees table as needed.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to select only rows with max value on a column?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?
4. How to concatenate text from multiple rows into a single text string in MySQL?
5. How to get the rows which have the max value for a column for each group in another column in SQL?
6. How to select first row in each GROUP BY group?