Error Using Delete Query

[bigmerf]

I have two tables that are identical. One table is for current orders, and the other is for historical purposes.

My dilema is this: I have about 1,000 records in my current orders table that also exist in my history table. I want to delete all of the "matching" records from the current table seeing how they are already in the history table.

I've created a "delete query", where I select every field from the table I want to delete, and I've linked the two table together so that I only deleted those records from the current table that match the same SO# from the history table.

Every time I try to run the query, it gives me a message stating: "Specify the table containing the records you want to delete." Why am I getting this error message? Should I not link the tables together? Do I need to add criteria for each field in the table?

Essentially I want to delete all of the duplicate values from the current table. Can anyone help??

Thanks!

 

[PHV]

DELETE C.*
FROM [current orders] AS C INNER JOIN [historical table] AS H
ON C.[SO#] = H.[SO#]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bigmerf]

I tried that and I get an error message stating: "Could not delete from the specified table." Is it because I'm auto-numbering the SO# field on the current table perhaps??

 

[PHV]

And this ?
DELETE FROM [current orders]
WHERE [SO#] In (SELECT [SO#] FROM [historical table])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bigmerf]

How sweet it is!! That worked!! A subquery huh? Why does it work with the subquery as opposed to INNER JOIN relationship we tried creating the first time? I've always learned that the server treats JOINS a lot faster than subqueries....

Thanks for the help!!