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', '[email protected]', 'manager');
INSERT INTO `customer` (`cus_id`, `full_name`, `phone`, `email`, `cus_type`) VALUES(2, 'Jill', '323 443 4432', '[email protected]', 'employee');
INSERT INTO `customer` (`cus_id`, `full_name`, `phone`, `email`, `cus_type`) VALUES(3, 'Paul', '664 442 2333', '[email protected]', 'contract');
INSERT INTO `customer` (`cus_id`, `full_name`, `phone`, `email`, `cus_type`) VALUES(4, 'Kim', '412 442 0009', '[email protected]', '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

Be the first to comment

Leave a Reply