JOIN mysql for updating the table

Question

I have two tables in the mysql as follows:

Table_A

Table_B

I need to join Table_B to Table_A and add the values of BalAmountLC to Table_A where the GLAccount equals. I also need to update other GLAccount values to Table_A which is not exist in Table_A.

Anyone please advise me which is the best way to do it. I feel like I need to use RIGHT JOIN. But I am not sure about that.

I am stuck with the following query:

SELECT Table_A.*,Table_B.*,Table_A.BalAmountLC+Table_B.BalAmountLC as sum FROM Table_A RIGHT OUTER JOIN Table_B ON Table_A.GLAccount=Table_B.GLAccount

Thanks.

Answer 1

So basically you need to INSERT all GLAccount in Table_B to Table_A if it does not exit in Table_A. If exists, you want to add BalAmountLC to Table_A

 UPDATE Table_A INNER JOIN Table_B ON (Table_A.GLAccount = Table_B.GLAccount)
 SET Table_A.BalAmountLC = Table_A.BalAmountLC + Table_B.BalAmountLC;

 INSERT INTO Table_A SELECT Table_B.* FROM Table_B LEFT JOIN Table_A
 ON (Table_B.GLAccount = Table_A.GLAccount) WHERE
 Table_A.GLAccount IS NULL;