Sometime we need to update data on database table that contain related type column so we use update query with inner join
UPDATE tbl_payslip
INNER JOIN
tbl_payslip_master ON tbl_payslip.emp_code = tbl_payslip_master.emp_code
SET
tbl_payslip.Conv_Trans_Allow=tbl_payslip_master.Conv_Trans_Allow
On this example we have two table first one is tbl_payslip_master and second is tbl_payslip
First we create Salary Master table name tbl_payslip_master
CREATE TABLE `tbl_payslip_master` (
`emp_code` varchar(50) NOT NULL,
`emp_name` varchar(50) NOT NULL,
`Conv_Trans_Allow` int(11) NOT NULL,
`Salary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tbl_payslip_master` (`emp_code`, `emp_name`, `Conv_Trans_Allow`, `Salary`) VALUES
('1', 'Raj', 3000, 60000),
('2', 'Savi', 2500, 50000),
('3', 'Swara', 1200, 45000);
Now we create second table tbl_payslip
CREATE TABLE `tbl_payslip` (
`emp_code` varchar(50) NOT NULL,
`month` varchar(10) NOT NULL,
`Conv_Trans_Allow` int(11) NOT NULL,
`salary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tbl_payslip` (`emp_code`, `month`, `Conv_Trans_Allow`, `salary`) VALUES
('1', 'Jan', 0, 60000),
('2', 'Jan', 0, 50000),
('3', 'Jan', 0, 45000);
Table tbl_payslip_master looks like
Table tbl_payslip looks like
Now I want to update tbl_payslip.Conv_Trans_Allow with tbl_payslip_master.Conv_Trans_Allow so run below code
UPDATE tbl_payslip
INNER JOIN
tbl_payslip_master ON tbl_payslip.emp_code = tbl_payslip_master.emp_code
SET
tbl_payslip.Conv_Trans_Allow=tbl_payslip_master.Conv_Trans_Allow
Now tbl_payslip looks like
