Can an update query set values selected from another table?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deko - 27 Feb 2005 18:47 GMT

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.

Reply to this Message

pietlinden@hotmail.com - 28 Feb 2005 02:30 GMT

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.

Reply to this Message

Trevor Best - 28 Feb 2005 08:14 GMT

> I know I can use Inner Joins in an Update query like this:
>
[quoted text clipped - 10 lines]
>
> 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/downloads/baslookup.zip) function
in place of the subquery.

Signature

This sig left intentionally blank

Reply to this Message

deko - 28 Feb 2005 11:45 GMT

> > I know I can use Inner Joins in an Update query like this:
> >
[quoted text clipped - 15 lines]
> http://easyweb.easynet.co.uk/~trevor/downloads/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(parameter);

where the function interates over the new vaules.

Reply to this Message