You can update a MySQL table’s column with the values from another table using select query. This will be helpful in cases like if you want to update the changes made to one table to another table.
UPDATE [table_name] SET [column_name] = (SELECT [column_name] FROM [table_name] WHERE [column_name] = [value]) WHERE [column_name] = [value];
Basically we have an UPDATE query which selects and updates the value from another table using SELECT subquery
Assume you have two tables called “Products” and “Sold” like below.
In Products table, the value of the product_name of the first row is “Smart Watch“. But, in Sold table the value is “Watch” for the same product_id.
So you update the value of Products table to Sold table like this.
UPDATE Sold SET product_name = (SELECT product_name FROM Products WHERE product_id = 1) WHERE product_id = 1;
This output will be like this.
Update with CONCAT()
We learnt to update a column with another table values using SELECT subquery, You can also select two values from two different tables and join them using CONCAT() and then update the value.
You can select brand_name from Brands table and product_name from Products table and join them using CONCAT() then update the value to Sold table like below.
UPDATE Sold SET product_name = CONCAT((SELECT brand_name FROM Brands WHERE brand_id=1),' - ',(SELECT product_name FROM Products WHERE product_id = 1)) WHERE product_id = 1;
This will update the Sold table like this.
If you want, you can also INSERT a new row with the values from SELECT query.
Use this queries and give your feedback. Share this post if you find it useful.