Update One Table with Another Table's Values in MySQL

Sometimes we need to update a table data with the values from another table in MySQL. Doing this helps in efficiently updating tables while also maintaining the integrity of the database. This is mostly used for automated updates on tables.

We can update values in one table with values in another table, using an ID match. ID match ensures that the right values are updated.

To efficiently update a table with values of another table based on an ID match we use UPDATE Statement with tables. Let's discuss the MySQL queries to perform this operation.

Using UPDATE and JOIN to Update Table Based on ID Match

We can use the UPDATE statement in MySQL and the JOIN clause to update records in one table based on a matching ID from another table.

Syntax

UPDATE table1

JOIN table2 ON table1.common_id = table2.common_id

SET table1.column_to_update = table2.new_value;

Parameters:

• table 1 is the table you want to update.
• table 2 is the table containing the new values.
• common_id is the shared identifier between the two tables.
• column_to_update is the specific column you want to update.
• new_value is the new value you want to set.

Examples

Let's first create two tables.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(10, 2)
);

CREATE TABLE salary_updates (
  emp_id INT PRIMARY KEY,
  new_salary DECIMAL(10, 2)
);
INSERT INTO employees VALUES 
  (1, 'John Doe', 50000.00),
  (2, 'Jane Doe', 60000.00);

INSERT INTO salary_updates VALUES 
  (1, 55000.00),
  (2, 65000.00);

We will be updating the employee table in this example. Let's look at the original table values:

Let's update the salary values in the employee table with new_salary values in salary_update table.

The common identifier is the employee ID (emp_id) and we want to update the salary column in the employees table with the new salary values from the salary_updates table.

Now, we will use the following query to perform the UPDATE operation:

UPDATE employees
JOIN salary_updates ON employees.emp_id = salary_updates.emp_id
SET employees.salary = salary_updates.new_salary;

After running this query, the salary column in the employees table will be updated based on the matching employee IDs in the salary_updates table.

Output:

Conclusion

In conclusion, the MySQL UPDATE statement with the JOIN clause can be used to update one table data to another table in MySQL using an ID match. This is an efficient way of synchronizing or transferring information. This method makes data updates faster and ensures consistency across tables by using shared identifiers. This is a useful feature for maintaining data consistency in relational databases.