Delete duplicate records

[jescat]

Hello,

I have two tables, and would like to delete records out of the second table if they are found in the first based on a key of three fields. Take the following example the first threee columns make up the key of each table:

Table 1 Table 2
1 1 1 3 1 1 1 4
2 2 2 4 1 2 1 3
3 3 3 1 2 2 5 9
3 3 6 8 3 3 3 5

I would like to delete the 1st and 4th record out of table2. Is there a way to build a query to do this using Access97 query builder or do you have to write an SQL statement literally.

Thanks

 

[mdthornton]

You can do this in the query builder. Just add the 2 tables, make the joins between your 3 key columns and then set the query type to delete and double click * on table 2. Best Regards,
Mike

 

[jescat]

Mike,

That is what I thought as well. However, when I do that I get the error stating "Operation must use an updatable query". If I build this query as a select query it finds the records that I need deleted, but when I change it to a delete query I get the error I mentioned. Any other thoughts?

JesCat

 

[mdthornton]

To be honest I've had this problem a lot. Sometimes the delete works, sometimes not. Make sure your keys are the same in both tables particularly the one you're deleting from.

My workaround for this entails selecting the rows I want
to keep into a tmp table, delete all rows from original
and then reload the saved rows.

You might also try executing the delete sql. In VB make up a string equivalent to the SQL view of your delete and then do: currentdb.execute mySQL

This often works for simpler deletes.
Best Regards,
Mike

 

[cmmrfrds]

Generally speaking in SQL you cannot delete(safely) when joining tables, although Access provides some exceptions with the dynaset and inconsistent updates. Access is protecting you by not allowing the delete. In ADO you can specific which table will be deleted through a property, so the delete will work on joined tables.

You can avoid a join by using a subquery which will allow the delete to happen.

'--assumes a unique id column
delete from table1 a
where a.id In (select b.id from table2 b where a.key1 = b.key1
and a.key2 = b.key2 and a.key3 = b.key3 )

Usually, you can take a query like above and paste it into the query builder under view sql and it will show you how you would need to setup in the query builder. Test the query by making the delete an select to start with then check out your data and change back to delete.

 

[jescat]

Thanks Guys,

Currently I have exactly what cmmrfrds proposed and it seems to be working I was just hoping there was a way to build these queries to save time.

Thanks for the help

JesCat