How to create an update query from joined tables

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

Guest

I am trying to update a table based on the join of two other tables. In SQL
Server this syntax works:

UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id

what the equivalent in Access?

Thanks for help in advance,

WayneM
 
Hi,


UPTADE titles INNER JOIN sales ON titles.title_id=sales.title_id
SET titles.fieldName = sales.fieldName


Since it seems you try to get a SUM, why not using a total (GroupBy) query?


Jet does not behave like MS SQL Server on many points. Among others, you can
update on more than one table at a time, with Jet, that is why the syntax
describe the join, then describe what is to be updated. Also, Jet does not
wrap the update in a kind of micro-transaction. An example is better that a
literal description:


UPDATE TableX SET a=b, b=a


in MS SQL Server, that EXCHANGES the two columns; but in Jet, that makes
both columns now equal to what was initially under column b: Jet does not
use a temporary copy of the record before making the update and then,
reading from that buffer to get the assigned values... (mainly a consequence
of allowing update of multiple tables).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top