How to delete records in one table based on list in second table

[Maturi]

Hi,

I have 2 tables

tbl_Docs with Fields:
- ID1 (Autonumber)
- DocRef (Integer)
- Others (Text)

tbl_DeleteList with fields
- ID2 (Autonumber)
- DocToDelete (Integer)

I want to write a query to delete those records in tbl_Docs where the DocRef matches the list in tbl_DeleteList (field DocToDelete)

I know how to do the simple delete for a single record

DELETE tbl_docs.* FROM tbl_docs WHERE (((tbl_docs.DocRef)=2))

but I can't get it to work by reading the list in tbl_DeleteList. I've tried.....

DELETE tbl_Docs.*, tbl_Docs.DocRef FROM tbl_Docs INNER JOIN tbl_DeleteList ON tbl_Docs.DocRef = tbl_DeleteList.DocToDelete
WHERE (((tbl_Docs.DocRef)=[tbl_DeleteList]![DocToDelete]))

And

DELETE tbl_Docs.*, tbl_Docs.DocRef  FROM tbl_Docs, tbl_DeleteList WHERE (((tbl_Docs.DocRef)=[tbl_DeleteList]![DocToDelete]))

Neither work - Can you help?

Thanks

 

[gmmastros]

What type of database are you using? Access, SQL Server, etc... The syntax may be different depending on the database.

This should work for SQL Server (and possibly Access).

DELETE tbl_Docs
FROM   tbl_Docs 
       INNER JOIN tbl_DeleteList 
         ON tbl_Docs.DocRef = tbl_DeleteList.DocToDelete

Of course, you should test it first by selecting the data.

Select *
FROM   tbl_Docs 
       INNER JOIN tbl_DeleteList 
         ON tbl_Docs.DocRef = tbl_DeleteList.DocToDelete

You don't need the where clause because you are already inner joining with an ON clause.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Maturi]

George

This in Access 2003 - I think I should have posted this in Access VBA coding forum!!

I derived the code form the Access Query builder

I tried the following..

DELETE tbl_Docs.* FROM tbl_Docs INNER JOIN tbl_DeleteList ON [tbl_Docs].[DocRef]=[tbl_DeleteList].[DocToDelete]

and I got the error message
"Could not delete from specifed tables"

 

[gmmastros]

Actuall, I suggest you post this in forum701 Microsoft: Access Queries and JET SQL.

I also suggest you check your database for foreign key relationships.

If you have another table that is linked to tbl_Docs with a parent/child relationship, then you would need to delete the records from the child table first.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

You can also do something like

Delete from tblName where id in (select id from deletelist)

can't you?

Bob