mscertified said:
Does anyone know the SQL syntax for updating a table based on data and column
comparisons with a second table.
I looked in Access help and googled it but could not find anything.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Usual syntax is
UPDATE <table1> INNER <or LEFT/RIGHT> JOIN <table2>
SET <table?>.<column> = <table?>.<column>
WHERE <criteria>
or
UPDATE <table1>
SET <column> = (SELECT <column> FROM <table2> WHERE <criteria>)
<criteria> in the 2nd example would be something like this:
WHERE <table1>.<column> = <table2>.<column>
You'd substitute your table names for <table?> and your column names for
<column>
In Access/JET you can't do this in the SET clause
SET <column> = (SELECT Sum(<column>) FROM <table2>)
because the subquery it is not an updateable query. You can use domain
aggregate functions instead (DSum(), DLookup(), etc.).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQw4QGIechKqOuFEgEQIFXwCfSNut/2Ol31GJgpXKs57u5eNCi6kAoP1S
pmusBP8aJnSRugTHkTtDtIuM
=k14N
-----END PGP SIGNATURE-----
