Self joined table in a delete query


D

DW

I am attempting to delete duplicate records of the same table, where they are
only different on one field, by making a duplicate of the table, and doing a
self join in a delete query. The query is unable to determine which copy of
the table to delete from. How do you specify which table to delete from
either in design view or in SQL?
 
Ad

Advertisements

T

Tom van Stiphout

On Wed, 24 Dec 2008 04:36:03 -0800, DW <[email protected]>
wrote:

I'm not entirely sure what you want to do, but to distinguish the two
tables in a self-join you give them both an alias:
select * from tblEmployees as Indians
inner join tblEmployees as Chiefs
on Chiefs.EmpID = Indians.ManagerID
(etc.)

-Tom.
Microsoft Access MVP
 
Ad

Advertisements

J

John W. Vinson

I am attempting to delete duplicate records of the same table, where they are
only different on one field, by making a duplicate of the table, and doing a
self join in a delete query. The query is unable to determine which copy of
the table to delete from. How do you specify which table to delete from
either in design view or in SQL?

By explicitly including the table name in the DELETE clause:

DELETE Copy1.*
FROM tablename As Copy1
INNER JOIN tablename As Copy2
ON ...

Note that a Totals query (with a Count) won't be updateable; perhaps you could
post your SQL.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top