Query help, please

A

Anthony

Is it possible to UPDATE more multiple tables in a *single* UPDATE Query?
Something like:


docmd.runsql("UPDATE MyTable1, MyTable2, MyTable3 SET MyTable1.MyField1 =
MyValue1, MyTable2.Myfield2 = MyValue2, MyTalbe3.MyField3 = MyValue3 WHERE
Criteria")

??

Ant
 
J

John Vinson

Is it possible to UPDATE more multiple tables in a *single* UPDATE Query?
Something like:


docmd.runsql("UPDATE MyTable1, MyTable2, MyTable3 SET MyTable1.MyField1 =
MyValue1, MyTable2.Myfield2 = MyValue2, MyTalbe3.MyField3 = MyValue3 WHERE
Criteria")

??

Ant
Click to expand...

No. You can run three queries in succession.

John W. Vinson[MVP]
 
6

'69 Camaro

Hello, Steve of Trey Davis fame.
Is it possible to UPDATE more multiple tables in a *single* UPDATE Query?
Click to expand...

Yes. One must use an ANSI SQL join, not a Cartesian join, and have a common
column in all three tables. Try:

UPDATE (TableA INNER JOIN TableB ON TableA.State = TableB.State) INNER JOIN
TableC ON TableB.State = TableC.State
SET TableA.MyValue1 = "111", TableB.MyValue2 = "222", TableC.MyValue3 =
"333"
WHERE (TableA.ID = 2);

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
Top