UPDATE table_name
SET column1 = (SELECT column2 FROM another_table WHERE condition)
WHERE condition;
If you want to do any manipulation with the data of MySQL database table. Like insert, update, select, and delete, these statements are available in MySQL. But you want to select in the update query itself. So for this, you have to create a query using MySQL.
In this tutorial, you will learn how to create select, and update in the same query mysql to modify and fetch data within a MySQL database.
Select and update in same query MySQL
Using the MySQL UPDATE FROM SELECT statement, you can update data in one table based on the values retrieved from another table.
Update from Select statement is used to update large amounts of data with a single query. It can be used to update one or multiple columns in a table with values retrieved from another table. This statement can be especially useful when you need to update data in a table based on the results of a complex query.
Syntax of the UPDATE FROM SELECT statement:
Here is the syntax of the UPDATE FROM SELECT statement in MySQL:
UPDATE table1 SET column1 = (SELECT column2 FROM table2 WHERE condition) WHERE condition;
In this syntax, table1 is the name of the table you want to update, column1 is the name of the column you want to update, and table2 is the name of the table that you want to select data from. column2 is the name of the column that contains the values you want to update, and condition is the condition that determines which rows should be updated.
Example update from select MySQL
Let’s take an example to understand the UPDATE FROM SELECT statement better.
Suppose you have two tables, employees and salaries. The employees table contains the employee_id, first_name, last_name, and salary columns, while the salaries table contains the employee_id and salary columns.
And you want to update the salary column in the employees table with the values obtained from the salaries table. you can do this using the below given MySQL update from select query:
UPDATE employees SET salary = (SELECT salary FROM salaries WHERE employees.employee_id = salaries.employee_id) WHERE EXISTS (SELECT 1 FROM salaries WHERE employees.employee_id = salaries.employee_id);
In this query, you are updating the salary column in the employees table with the values obtained from the salary column in the salaries table. The condition employees.employee_id = salaries.employee_id ensures that only the salaries of matching employees are updated. The EXISTS subquery is used to avoid updating rows where there is no match in the salaries table.
Conclusion
That’s it, in this tutorial, you have learned how to use update and select statements in the same query in Mysql. By using this statement, developers can simplify their code and improve the performance of their database queries.
