RethinkingWeb | How to use JOIN in MYSQL Update Statement. - RethinkingWeb
RethinkingWeb offers a multitude of web software development and digital marketing consulting services that enables you to out perform your competition
software development company, custom software development in thane, software development company in thane, mumbai software developers, PHP software development house, software developers, Digital Marketing Companies, Customized Software Quality Management, Customized Software Development Company Profile,Custom Software Applications,Build Custom Website,Affordable Custom Websites,Customized Software Definition,Free Website Design
4324
single,single-post,postid-4324,single-format-standard,ajax_leftright,page_not_loaded,smooth_scroll,,wpb-js-composer js-comp-ver-3.6.12

Blog

How to use JOIN in MYSQL Update Statement.

Databse using Join in my sql

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                                      Join in

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

Databse using Join in my sql                        1

 

 

 

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

join `customer`

as c on c.cust_id = z.cust_id

SET z.zone = ‘EAST’ WHERE c.cust_name like ‘S%’

 

3

 

 

 

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

 

 

Post A Comment