Updating record in one table with record in another table

Updating record in one table with record in another table

Post by John » Thu, 05 Jun 2008 12:12:55


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
 
 
 

Updating record in one table with record in another table

Post by John W. Vi » Thu, 05 Jun 2008 14:37:06


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 [MVP]

 
 
 

Updating record in one table with record in another table

Post by John » Thu, 05 Jun 2008 15:12:01

Would this work?

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

Updating record in one table with record in another table

Post by John Spenc » Thu, 05 Jun 2008 22:38:20

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
'====================================================
 
 
 

Updating record in one table with record in another table

Post by John W. Vi » Fri, 06 Jun 2008 01:46:28


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 W. Vinson [MVP]
 
 
 

Updating record in one table with record in another table

Post by John » Fri, 06 Jun 2008 11:04:12

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