Update SQL

G

Gus Chuch

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
 
A

Allen Browne

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]), ...
 
M

Michel Walsh

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
 
J

John Spencer

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
..
 
Top