How to delete data that is duplicated in two different tables?

[Slugmaster]

I have two tables of the same structure in an Access DataBase. One table is linked from an ODBC source and the other is an Access table. I want to delete the data in the Access table that contains duplicate information from the ODBC table. The tables are joined by the primary key.

When I try to do this, I get an error:
Operation must use an updatable query. (Error 3073).

Here is the SQL:
DELETE fvm_CalResults.*
FROM fvm_CalResults INNER JOIN mt_CalResults ON fvm_CalResults.CTAG = mt_CalResults.CTAG;

What am I doing wrong?

 

[bosully]

I don't know what you're doing wrong as I'm crap at using INNER JOINs. On the other hand, this is how I would do it:

DELETE *
FROM fvmCalResults
WHERE CTAG IN (Select CTAG from fvmCalResults, mt_CalResults
Where fvmCalResults.CTAG = mt_CalResults.CTAG);

I've used similar code and it does produce the desired results (assumed here that fvmCalResults is your Access table that you want to clear out and mt_CalResults is your Oracle table that you don't want to touch).

Give it a go (on a demo database) and see if it does the trick,

Brendan

 

[Slugmaster]

Brendan,

I did as you suggested, on a demo database and your SQL suggestion. The result is another error:

The specified field <field> could refer to more than one table listed in the FROM clause of your SQL statement. (Error 3079)

So I tried the following:

DELETE fvmCalResults.*
FROM fvmCalResults
WHERE CTAG IN (Select CTAG from fvmCalResults, mt_CalResults
Where fvmCalResults.CTAG = mt_CalResults.CTAG);

But got the same error. Any ideas? I can email you the sample Access 97 database. It is not very big.

 

[bosully]

Sorry not to get back sooner. Friday night etc,

I had a look and I think the problem is that we're not identifying which CTAG is intended in the last two lines of the code and that seems to be confusing things.

Try the following -

DELETE *
FROM fvmCalResults
WHERE fvmCalResults.CTAG IN
(Select fvmCalResults.CTAG from fvmCalResults, mt_CalResults
Where fvmCalResults.CTAG = mt_CalResults.CTAG);

I think it should do the trick. If it doesn't, you can send on the database to bosully@gofree.indigo.ie and I'll have another look.

Brendan