Get a column data from one table and update it to another column from another table

I have two tables, tbl_Invoice and tbl_tax.

tbl_invoice

id | amount | tax
------------------
01 | 150.00 | 2.5
02 | 250.00 | 3.2
03 | 350.00 | 1.5

tbl_tax

id | inv_id | tax
-----------------
01 | 01     | 2.6
02 | 02     | 5.2
02 | 03     | 6.2

I have to update the tbl_Invoice tax column with the tbl_tax tax column, end results should be like

tbl_invoice

id | amount | tax
------------------
01 | 150.00 | 2.6
02 | 250.00 | 5.2
03 | 350.00 | 6.2

CodePudding user response：

UPDATE tbl_invoice Inv
LEFT JOIN tbl_tax Tax ON Inv.id = Tax.inv_id 
SET Inv.tax = Tax.tax

Reference MySQL UPDATE JOIN

CodePudding user response：

To update such data use a subquery and fetch the tax from you tbl_tax table taking the common inv_id from tbl_tax and id from tbl_invoice fields.

update 
tbl_invoice ti 
set 
ti.tax=(select tt.tax from tbl_tax tt where tt.inv_id=ti.id)

Or you can use a join to update your records:

UPDATE tbl_invoice ti
INNER JOIN `tbl_tax` tt ON  tt.`inv_id`=ti.`id`
SET ti.`tax`=tt.`tax`