How to update a table using a join in mySQL

Some times we come across scenarios where we need to update a table¬† or more than one table based on a join condition between 2 or more tables. I know this sounds complex but its possible in mySQL and believe me its a life saver. This feature is also know as “update join”.

Suppose you have two tables

CREATE TABLE `customer` (
`cus_id` int(11) NOT NULL,
`full_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`phone` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`cus_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_payment` (
`cus_id` int(11) NOT NULL,
`cus_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`cus_salary` int(11) NOT NULL,
`cus_commission` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

After creating the above tables run the following SQL so that the data is populated

INSERT INTO `customer` (`cus_id`, `full_name`, `phone`, `email`, `cus_type`) VALUES(1, 'Jack', '454 443 3321', 'jack@test.com', 'manager');
INSERT INTO `customer` (`cus_id`, `full_name`, `phone`, `email`, `cus_type`) VALUES(2, 'Jill', '323 443 4432', 'jill@test.com', 'employee');
INSERT INTO `customer` (`cus_id`, `full_name`, `phone`, `email`, `cus_type`) VALUES(3, 'Paul', '664 442 2333', 'paul@test.com', 'contract');
INSERT INTO `customer` (`cus_id`, `full_name`, `phone`, `email`, `cus_type`) VALUES(4, 'Kim', '412 442 0009', 'kim@test.com', 'employee');

 

INSERT INTO `customer_payment` (`cus_id`, `cus_type`, `cus_salary`, `cus_commission`) VALUES(1, 'manager', 100000, 0);
INSERT INTO `customer_payment` (`cus_id`, `cus_type`, `cus_salary`, `cus_commission`) VALUES(2, 'employee', 45000, 5000);
INSERT INTO `customer_payment` (`cus_id`, `cus_type`, `cus_salary`, `cus_commission`) VALUES(3, 'contract', 35000, 15000);
INSERT INTO `customer_payment` (`cus_id`, `cus_type`, `cus_salary`, `cus_commission`) VALUES(4, 'employee', 25000, 5000);

Now we need to update the cus_type column in the customer_payment table from the cus_type column in the customer table. This is where we will write an update join query which will update the relevant records based on a join condition.  After running the following SQL query the customer_payment table will be updated and our example is complete.

update customer_payment cp
inner join customer c on cp.cus_id = c.cus_id
set cp.cus_type = c.cus_type;

This example had very few records for testing but its a gem where you have thousands of records to update. This will save you a lot of time and effort.

Hope the above helped. If you would like to read more then here is some detailed information regarding update join

Please follow and like us:

Be the first to comment

Leave a Reply

Your email address will not be published.


*