Update random values from field

[Maarkr]

Two tables... one is a lookup table with countries, like USA, UK, Canada,
Australia. The other is a table of names, country, etc, with only names
filled in. I want to update the null country values with random country
values from the lookup table. (generating a table of dummy values from
lookup tables) I tried this but it said the set is not updateable:
UPDATE Countries INNER JOIN Members ON Countries.Country= Members.Country
SET Members.Country = (SELECT top 1 Countries.Country FROM Countries ORDER BY
Rnd([CountryNo])
WHERE (((Members.Country) Is Null));

Or if u know of another way to enter the country values from the lookup into
the empty field. Thx

 

[karl dewey]

UNTESTED -- BACKUP DATABASE - BACKUP DATABASE -Try this --

UPDATE Countries LEFT JOIN Members ON Countries.Country= Members.Country
SET Members.Country = (SELECT top 1 Countries.Country FROM Countries ORDER
BY Rnd([CountryNo]))
WHERE (((Members.Country) Is Null));