How to use JOIN in MYSQL Update Statement.
How to use JOIN in MYSQL Update Statement
I was recently searching for a way to update a field in one database table, based on information in another database table. After many searches i found that it can be done using JOIN statement. We can JOIN the two database tables based on the common field.
Let us see an example to use
JOINS with UPDATE statement.
We are going to use a database which contains two tables
Customer table stores the Customer Information having fields cust_id, cust_name
Zone table stores Zone Information having fields zone_id, cust_id and zone
Here Customers are mapped to a particular Zone
Suppose you want to update the zones for customers whose name starts with character ‘S’.
The cust_id is stored in Zone table therefore you can use UPDATE statement to update the zones in Zone table based on the names of customers in Customer table.
See the following query :
UPDATE `zone` as z
as c on c.cust_id = z.cust_id
How the query works:
We specify the zone table first after the UPDATE clause because we want to update the Zone table only.
For each customer, the query check the customer name which starts with character ‘S’. If it finds a match then it updates the zone column in Zone table