Updating record in one table with record in another table

[John]

Hi

How can I update a record with id = x in one table with another record with
id = y from a second table?

Thanks

Regards

 

[John W. Vinson]

Hi

How can I update a record with id = x in one table with another record with
id = y from a second table?

Thanks

Regards

That's awfully vague, but probably a Subquery will work:

UPDATE onetable
SET [the field you want to update] = (SELECT [the field you want to update
from] FROM secondtable WHERE id = y)
WHERE id = x;

 

[John]

Would this work?

UPDATE onetable, secondtable SET secondtable.Tel = onetable.tel,
secondtable.Fax = onetable.fax
WHERE (secondtable.ID = 3) AND (onetable.ID = 4)

Hi

How can I update a record with id = x in one table with another record
with
id = y from a second table?

Thanks

Regards

That's awfully vague, but probably a Subquery will work:

UPDATE onetable
SET [the field you want to update] = (SELECT [the field you want to update
from] FROM secondtable WHERE id = y)
WHERE id = x;

 

[John Spencer]

It might. Why not try it and see if it works.

Or use the following.

UPDATE SecondTable
Set Fax = DLookup("Tel","OneTable","Id=4")

I think John Vinson's solution will fail, since there is the possibility
of the subquery returning more than one record (as far as Access is
concerned) and therefore the update query will error.

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

Would this work?

UPDATE onetable, secondtable SET secondtable.Tel = onetable.tel,
secondtable.Fax = onetable.fax
WHERE (secondtable.ID = 3) AND (onetable.ID = 4)

Hi

How can I update a record with id = x in one table with another record
with
id = y from a second table?

Thanks

Regards

That's awfully vague, but probably a Subquery will work:

UPDATE onetable
SET [the field you want to update] = (SELECT [the field you want to update
from] FROM secondtable WHERE id = y)
WHERE id = x;

 

[John W. Vinson]

Would this work?

UPDATE onetable, secondtable SET secondtable.Tel = onetable.tel,
secondtable.Fax = onetable.fax
WHERE (secondtable.ID = 3) AND (onetable.ID = 4)

Hi

How can I update a record with id = x in one table with another record
with
id = y from a second table?

Thanks

Regards

That's awfully vague, but probably a Subquery will work:

UPDATE onetable
SET [the field you want to update] = (SELECT [the field you want to update
from] FROM secondtable WHERE id = y)
WHERE id = x;

wow... three guys named John in the same thread...

John Spencer is right, I had a brainfade on that; the subquery will NOT work.
But your Cartesian join won't work either, since (as far as Access is
concerned) every record in onetable will be paired with every record in
secondtable. The criteria in the WHERE clause won't be enough to allow
updatability.

It would help to know a bit more about the actual situation - are the two
tables related in any way? How is the choice of ID's defined?

 

[John]

Hi John

There is only one record each under that criteria in each table as the
values given are the primary keys.

Seems to be working.

Regards

Would this work?

UPDATE onetable, secondtable SET secondtable.Tel = onetable.tel,
secondtable.Fax = onetable.fax
WHERE (secondtable.ID = 3) AND (onetable.ID = 4)

Hi

How can I update a record with id = x in one table with another record
with
id = y from a second table?

Thanks

Regards


That's awfully vague, but probably a Subquery will work:

UPDATE onetable
SET [the field you want to update] = (SELECT [the field you want to
update
from] FROM secondtable WHERE id = y)
WHERE id = x;

wow... three guys named John in the same thread...

John Spencer is right, I had a brainfade on that; the subquery will NOT
work.
But your Cartesian join won't work either, since (as far as Access is
concerned) every record in onetable will be paired with every record in
secondtable. The criteria in the WHERE clause won't be enough to allow
updatability.

It would help to know a bit more about the actual situation - are the two
tables related in any way? How is the choice of ID's defined?