**update statement**

  • Thread starter Thread starter R-M
  • Start date Start date
R

R-M

Hi

I am working with Access 2003 and I want to write an update query to
update the values of one table from another one.

update a
set a.f1=b.f1
from a,b
where a.no=b.no

the above sql works in SQl 2000 it will encountered with error in access.
how can I run a similar statement like this in access?
any help would be thankful.
 
I am working with Access 2003 and I want to write an update query to
update the values of one table from another one.

update a
set a.f1=b.f1
from a,b
where a.no=b.no

the above sql works in SQl 2000 it will encountered with error in access.
how can I run a similar statement like this in access?
Click to expand...

Hi R-M,

Coming from SQL Server, this may seem strange...

but Access supports joins in update query.

UPDATE a INNER JOIN b
ON a.no = b.no
SET a.f1 = b.f1;

some other simple examples:

UPDATE tbl1 INNER JOIN tbl2
ON tbl1.PK = tbl2.PK
SET
tbl1.f1 = tbl2.f1,
tbl1.f2 = tbl2.f2;

UPDATE tbl1 INNER JOIN tbl2
ON tbl1.PK = tbl2.PK
SET
tbl1.f1 = tbl2.f1
WHERE tbl1.f1<>tbl2.f1;

good luck,

gary
 
Back
Top