Update SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can you do an update query using a SubQuery? I would like to update a table
using a query for the new values.
UPDATE “table1â€
SET “Column1†= (SubQuery.Column1) and “Column2†= (SubQuery.Column2)
WHERE table1ID = SubQueryID;
Can something like this be done?

thank You
 
I doubt that's going to work.

If you have a unique index on the field involved in the join, you could just
do it with an INNER JOIN.

Otherwise you may need to use something like DLookup() to get the value from
the other table:
UPDATE Table1
SET Col1 = DLookup("Col1", "Table2", "[ID] = " & [ID]), ...
 
If you update more than one column, use a coma, not AND:


UPDATE tableName
SET f1=33, f2=44
WHERE f1=44 AND f2=33


as example, will exchange values under f1 with those under f2, for the given
condition. The condition uses AND, but the SET clause uses coma.


Hoping it may help,
Vanderghast, Access MVP
 
This test query seems to work fine for me

UPDATE FAQ1 INNER JOIN
(SELECT fSubject, fText, fID FROM FAQ2) as F2
ON faq1.fid = f2.Fid
SET FAQ1.fSubject = [F2].[FSubject]
FAQ.tText = [F2].[FText]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top