Can an update query set values selected from another table?

[deko]

I know I can use Inner Joins in an Update query like this:

UPDATE DISTINCTROW tblA INNER JOIN qryA ON tblA.SomeID = qryA.SomeID SET
Flag = 0
WHERE (Flag = -1);

But I am specifying the new value to overwrite the old value, and just using
the join to narrow the set. If I don't know the new value and need to
select it from another unrelated table with no unique ID, can I still use an
update query?

In pseudo code it might look like this:

UPDATE tblSomeData SET [MyDate] = (SELECT [MyDate] FROM tblNewDates);

How else would I update an entire table with values from another unrelated
table?

Thanks in advance.

[pietlinden]

are you trying to update tblSomeData with a *single* value, or with
values that may vary? It looks like you're trying to update using
multiple values, but I can't be sure. In that case, you'd need an
inner join somewhere in your WHERE clause of the update query, which is
missing.

[Trevor Best]

deko wrote:

I know I can use Inner Joins in an Update query like this:

UPDATE DISTINCTROW tblA INNER JOIN qryA ON tblA.SomeID = qryA.SomeID SET
Flag = 0
WHERE (Flag = -1);

But I am specifying the new value to overwrite the old value, and just using
the join to narrow the set. If I don't know the new value and need to
select it from another unrelated table with no unique ID, can I still use an
update query?

In pseudo code it might look like this:

UPDATE tblSomeData SET [MyDate] = (SELECT [MyDate] FROM tblNewDates);

That will work if tblNewDates has one record, if not you must be more
selective in the subquery, or you can use DLookup() (or tLookup
http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) function
in place of the subquery.

--
This sig left intentionally blank

[deko]

> > I know I can use Inner Joins in an Update query like this:

UPDATE DISTINCTROW tblA INNER JOIN qryA ON tblA.SomeID = qryA.SomeID SET
Flag = 0
WHERE (Flag = -1);

But I am specifying the new value to overwrite the old value, and just using the join to narrow the set. If I don't know the new value and need to
select it from another unrelated table with no unique ID, can I still use an update query?

In pseudo code it might look like this:

UPDATE tblSomeData SET [MyDate] = (SELECT [MyDate] FROM tblNewDates);

That will work if tblNewDates has one record, if not you must be more
selective in the subquery, or you can use DLookup() (or tLookup
http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) function
in place of the subquery.

Thanks for the code. In a generic sense, then, the answer to my question
is:

UPDATE tblSomeData SET [MyDate] = Function(parame ter);

where the function interates over the new vaules.