Do you only have one record in Table1? Or do you want the field Total updated in ALL records with the sum of item from Table2 where ID = 123456? Is ID really a text field? Is there actually a matching ID in Table1? Is there a little more detail you could give us?
is one value which the sum of the
column item. One record in table1 should be upated. ID is a text field and there is a matching in ID table1. When I replace the select statement with a value the query works correctly. To me, the problem relies in the select statement. The syntax of the query seems to be fine.