Clorae Clorae - 1 month ago 4
MySQL Question

How to fill a column using another table's column in mysql

i have a table named tbl_employer

| id | employer | emp_type |
| 1 | aaa | GOVERNMENT |
| 2 | bbb | PRIVATE |


I want to join it with this table tbl_remit because the tbl_remit's data are

|RemitNo| id | employer | emp_cat |Amt_Remit| AP_From | AP_To |
| 1 | 1 | aaa | GOVERNMENT | 20.00 |01/01/2016|01/31/2016|
| 2 | 1 | aaa | GOVERNMENT | 10.00 |02/01/2016|02/29/2016|
| 3 | 1 | aaa | | 10.00 |03/01/2016|03/31/2016|
| 4 | 2 | bbb | PRIVATE | 50.00 |01/01/2016|01/31/2016|
| 5 | 2 | bbb | PRIVATE | 50.00 |02/01/2016|02/29/2016|
| 6 | 2 | bbb | | 50.00 |03/01/2016|03/31/2016|


I want to fill up the missing Data from tbl_remit based on tbl_employer.

|RemitNo| id | employer | emp_cat |Amt_Remit| AP_From | AP_To |
| 1 | 1 | aaa | GOVERNMENT | 20.00 |01/01/2016|01/31/2016|
| 2 | 1 | aaa | GOVERNMENT | 10.00 |02/01/2016|02/29/2016|
| 3 | 1 | aaa | GOVERNMENT | 10.00 |03/01/2016|03/31/2016|
| 4 | 2 | bbb | PRIVATE | 50.00 |01/01/2016|01/31/2016|
| 5 | 2 | bbb | PRIVATE | 50.00 |02/01/2016|02/29/2016|
| 6 | 2 | bbb | PRIVATE | 50.00 |03/01/2016|03/31/2016|


I used a Join Statement but failed.

SELECT r.RemitNo
, r.id
, r.employer
, (SELECT e.emp_type
FROM tbl_employer e
WHERE e.ID = r.ID) emp_cat
, amt_remit
, ap_from
, ap_to
FROM tbl_remit r
JOIN tbl_employer e
ON r.ID = e.ID

Answer

May be this will help:

UPDATE tbl_remit AS r
JOIN tbl_employer AS e ON e.employer=r.employer    
SET r.emp_cat=e.emp_type

Have in mind that if you have many records in the tables you may need add INDEX on employer fields in tables. I`m not 100% sure if r.id is equal to e.id and can be used for the JOIN but if it is ok then you can use this query:

UPDATE tbl_remit AS r
JOIN tbl_employer AS e ON e.id=r.id
SET r.emp_cat=e.emp_type

Your failing query in the question should look like this:

SELECT r.RemitNo
 , r.id
 , r.employer
 , e.emp_type
 , r.amt_remit
 , r.ap_from
 , r.ap_to 
FROM tbl_remit r
JOIN tbl_employer e 
ON r.ID = e.ID