update one table based on another

  • Thread starter Thread starter ljubo lecic via AccessMonster.com
  • Start date Start date
L

ljubo lecic via AccessMonster.com

Is it possible to write SQL update query that updates data in TABLE1 based
on data in TABLE2?
For example I need something like:(this is incorrect
but shows what I would like to do)

update table1
set amount = amount + ( select amount
from table2
where id = table1.id)
 
Sure. Just build an update query and include both tables in the query
design. In the UPDATE TO: field, just put your formula.

Rick B
 
The SQL String should be something like:

UPDATE Table1 INNER JOIN Table2
ON Table1.[ID] = Table2.[ID]
SET Table1.Amount = Table1.Amount + Table2.Amount

This assumes a One-to-(at most) One relationship between Table1 and Table2.
You need to check for the case that Table2 has more than 1 Record with the
same [ID].
 
Mr Dinh,

I have just tried the query but instead of adding values
it is merging them.(e.g. 10 + 1 = 101)
 
NO, No, Everything is fine. I made a mistake in a data
type (field amount set as text instead as number)

THANKS a LOT!!
 
Back
Top