Update a table with results of a subquery

[mikelawrence]

I would really appreciate advice on how to update a field in a table with a field from a 2nd table

In essence table1 and table2 have the same 2 fields - PIN and CardNbr. Both tables have PIN in so i can join the 2 tables on this field but only table1 has CardNbr in completely, how do i update CardNbr in table2 with the value of CardNbr in Table1 where CardNbr in table2 is null?

Thanks

Mike

 

[mymou]

Hi mikelawrence


Is this what you need?

UPDATE Table1 INNER JOIN Table2 ON Table1.PIN = Table2.PIN SET Table2.CardNbr = [Table1].[CardNbr]
WHERE (((Table2.CardNbr) Is Null));

Stew "Even a stopped clock tells the right time twice a day."

 

[THoey]

I hate joins....

UPDATE Table2
INNER JOIN Table1 ON Table2.Pin = Table1.Pin
SET Table2.CardNbr = Table1.CardNbr
WHERE (((Table2.Pin)=(Table1.Pin)));
Terry M. Hoey