SQL update with select statement
In the last chapter, we learned how to update data of the same table for single and multi-columns while values are provided by using the SQL update command.
In some scenarios, you may need to modify a table with the values from another table. In that case, you can use the SQL Update – select statement combination.
The update statement will specify the table to be updated along with column name(s) with the where clause while the Select statement queries the values from the other table(s).
SQL update with select example
To explain the update statement with the select command, consider this scenario. Suppose we have two databases
The test_db contains tbl_employee and test_db2 contain the tbl_customers tables. You can see structure and data in both tables below:
The scenario is, we want to update tbl_customers record of Mike (emp_age and emp_salary) from tbl_employee table’s record.
This is how SQL update select query will look like:
SET cus_age=i.emp_age ,
FROM (SELECT emp_age,emp_salary FROM test_db.tbl_employee where emp_id=1)i
As you can see, the tbl_customer’s cus_age and cus_salary columns are changed by using the select update combination.
Similarly, you can change the data in tables in the same database by using the select with the update. In that case, you don’t need to specify database name after choosing or setting a database, though.