Many to one update sql

Hello:

I am having a problem with the proper SQL to accomplish the following.

I am using MySQL. I have 2 tables, both tables contain 2 columns, column a and column b. In table 1 column a is the key and therefore unique. In table 2 the value in column a can be repeated many times.

What I want to do is update column b in table 1 from column b in table 2 from all rows in table 2 where t1.columna = t2.column a

Thanks in advance

You can do a JOIN in an UPDATE query...

I have tried as
UPDATE table1 t1, table2 t2
SET t1.columnb = t1.columnb + t2.columnb
WHERE t1.columna = t2.columna

This does not work

Please give more information than "this does not work".

Try with the explicit join syntax:
UPDATE table1 t1 INNER JOIN table2 t2 ON t1.columna = t2.columna
SET t1.columnb = t1.columnb + t2.columnb

I have tried both ways, This is the detai. Table 1 contains customers (customer number,....,arbalance) Table 2 contains invoice information (customer number,...,invoice amount). There can be several invoices in table 2 for any given customer in table 1. In my test I have created a customer file with only 1 customer, say customer 1. The starting arbalance is 0. The test invoice table contains 2 invoices, both for customer 1. (invoice 1, customer 1, amount 200 and invoice 2, customer 1, amount 300). I want to update the custoner tables arbalance with all matching invoices, in the test case there are only 2 and they both match. No mater how I try the update only the 1st invoice updates the customer file. After the update I expect that arbalance will be 500 (starting 0 + 200 + 300). It always equals 200.

Just curious why you want to store SUM value, since you always can call SUM() for this tasks?
For example, the below query will give the total invoice amount for each customer (number):


Furthermore, when and where did you expect to do the update call?
t1.columnb = t1.columnb + t2.columnb will not take into consideration what had already been added to the sum.

Hello:

I did find a solution very similar to what you suggested.

UPDATE t1
SET columnb = columnb + (SELECT SUM(columnb) FROM
t2 WHERE t1.columna = t2.columna)

Thanks