Updating one table from another table

[DZ]

I have two tables. Table1 and Table2.
Both have 3 fields in common: Name, Address and Location fields.

The combined value of the Name, Address and Locations fields make each
record unique.

Table1 has duplicate values of Name, Address and Location combined.
Table2 has unique values of Name, Address and Location combined.

I want to delete all the existing records in Table2, except records in
Table2 whose combined value of the Name, Address and Location fields already
exist in Table1.

In other words, I don't want to delete any existing records in Table2 that
have duplicates in the Name, Address and Location fields in Table1. I want
those and only those existing records in Table2 to remain untouched during
the delete process.

============================
Ultimately what i'm trying to do is to update Table2 from Table1, so that
Table2 contains all the combined (Name, Address and Location) values of
Table1 (which has duplicates). I want Table2 to have no duplicates and to
have certain records remain untouched during the delete process as I stated
above.

This is how i planned to do this.

1st Step - I would do a delete first as described above (that's what I
need help with).
2nd Step - I want to send all records from Table1 to Table2 except
duplicates in the Name, Address fields. I know how to do this second step. In
Table2, I would make the Name, Address and location fields a combined primary
key, too keep duplicates from being added to Table2. Then run an Append Query
from Table1 to Table2.


The reason for the delete process that deletes all records from Table2
except dups in Table1 is that Table2 must be a subset of Table1 and never
contain records whose combined value of the Name, Address and Location
fields is not in Table1 and previous updates from Table1 may have placed
records in Table2 that should not be there, because the current data in
table1 does not contain them.

Sorry for the longwinded explanation . I hope this clear !
Thanks for any ideas for how to accomplish the delete (Step 1)

 

[Lord Kelvan]

ok to calarify you want to table 2 to have all the distinct
combinations in table 1

the best method would be to purge table 2 and run a massive insert
into query

insert into table2 (name,address,location)
select distinct name,address,location
from table1

if table 2 has important information then a slightly more complex
query is needed

INSERT INTO table2 ( name, address, location )
SELECT DISTINCT table1.name, table1.address, table1.location
FROM table1
WHERE (((table1.id) Not In (
SELECT Table1.id
FROM Table1 INNER JOIN Table2 ON
(Table1.location = Table2.location)
AND (Table1.address = Table2.address)
AND (Table1.name = Table2.name))));

that one should to a dynamic update by checking what values in table1
dont exist in table2

 

[DZ]

Thanks for responding

Yes the purge should be done first, but Table2 has important information in
the other fields in records where Name, Address and Location combination
already exists in Table1, so I don't want to those record deleted... but all
other records I want deleted from table2.

I need help only with this selective delete process, step 1. Step 2 I think
I can handle.

Thanks

 

[Lord Kelvan]

so you want to delete from table 2 where they haev no corrisponding
records in table 1 ???

if so

delete *
from table2
where table2.id not in (
SELECT DISTINCT Table2.id
FROM Table1 INNER JOIN Table2
ON (Table1.name = Table2.name)
AND (Table1.address = Table2.address)
AND (Table1.location = Table2.location))

that will check the data in table2 and then see if it is in table 1 if
not it will delete it of course i am assuming your records have an ID
field

 

[DZ]

Thanks again

I want to delete from table 2 where they have no corrisponding
combined value of Name, Address and Location in table 1.

Table2's ID field is actually a primary key consisting of 3 fields: Name,
Address and Location.

Table1 also has Name, Address and Location fields but the combined value is
not unique in Table1. In table2, the combined value of the Name, Address and
Location fields is unique for each record.

Thanks again for anymore help with this.