MySQL – UPDATE a Column From SELECT Query Value

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.

MySQL Update From Select Query

You can also update a tables value from another table using INNER JOIN. But for this tutorial we are going to find how to do this with SELECT subquery

Syntax


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

Example

Assume you have two tables called “Products” and “Sold” like below.

MySQL update from select

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.

MySQL update from select

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.

MySQL update from select

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.

MySQL update from select

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.


Anand Roshan

Author: Anand Roshan

An entrepreneur, programmer and a passionate artist who loves to work independently. Get social: Twitter | Google + | Artist Page

Leave a Reply

Your email address will not be published. Required fields are marked *