updating table based on another table

[Guest]

Hi,
what's the exact syntax in Access for the following meta sql:
UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1

ie. I want to update tableA with values from tableB
Thanx

alek_mil

 

[Guest]

Alek,

UPDATE TableA as A
INNER JOIN TableB as B
ON A.ID = B.ID
SET A.[FieldName1] = B.[FieldName1], A.[FieldName2] = B.{FieldName2]

HTH
Dale

 

[John Spencer]

The syntax in Access (Jet) is as follows

UPDATE TABLEA INNER JOIN TableB
On TableA.a = TableB.a1
SET TableA.b = TableB.b1,
TableA.c = TableB.c1,
TableA.d = TableB.d1

If you have additional criteria you can add a where clause at the end.
WHERE TABLEA.a = "My Name"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..