Delete using a join

[R Bolling]

I am attempting to use a delete query based on a join where several
fields are involved with the join. The model is something like this:

Table1/Field1: A, B, C, D, E, F, G, H, I, J, K
Table2/Field1: C, D, F, H, K

What is the SQL syntax that I need to first, delete the values C, D,
F, H, & K from Table1, and then replace those values with the updates
from Table2?

My ignorant workaround is dumping Table2 into a keyed temporary table,
then dumping Table1 into it -- which works rather well for small
tables, but I would like to use a join to delete the updates from
Table1, and then replace them with the Table2 values. Any ideas for
this?

Thanks,

R. Bollin

 

[Michel Walsh]

Hi,


With Jet?

UPDATE table1 INNER JOIN table2 ON table1.field1=table2.field1
SET table1.whatever1 = table2.whatever1, table1.whatever2 = table2.whatever2


or, graphically, bring both tables in the designer, join them through their common field field1,
change the select query type to an update one (through the menu or the toolbar), bring the fields
from table1 to be updated in the grid, and under them, at the line UpdateTo, type
[Table2].[FieldToUpdateThem]



Hoping it may help,
Vanderghast, Access MVP