Update Table problem

[HeathRamos]

I am trying to update a table but I am doing the syntax wrong.
I want to join two tables using a one to one link for 4 fields, after which I
want to update the onhand field of one table with the onhand field of the
other.
Example:
Table1: Field1, Field2, Field3 Field4, Onhand
Table2: Field1, Field2, Field3, Field4, Onhand
I want to join both tables using fields 1-4 and the set the onhand of Table1 to
be the onhand of Table2.
Please help.

 

[ajayarora]

Hi,

You'll have to use sub-query in the update stmnt,

update table1 a
set a.onhand = ( select b.onhand
from table2 b
where a.field1 = b.field1
and a.field2 = b.field2
and a.field3 = b.field3 )

Assuming that table1 and table2 are having 1:1 relationships.

Regards
Ajay.