Deleting records from a table based on the relationship to another table

[camedeiros]

I would like to join two tables, and delete matching records from one of the tables.

Customer Id Address dues
Coca-cola 23 1 oak street $75

Customer Id Address dues City State
Coca-cola 23 1 oak street $75 Atlanta GA

Suppose that I would like to delete the record in the first table and substitute it with the second table's record, which has the same id.

Is there a way to do that? If we can't do it in one step, is there way for me to delete the record from the first table (by letting Access identify that record based on the join by ID and deleting it), so that I can get rid of the first one, and later paste the new one into the table?

Thanks!

 

[Pat Hartman]

Create a delete query for tblB. Then join tblB to tblA. Only the records in tblB that match records in tblA will be deleted.

Delete tblB.* From tblB Inner Join tblA On tblA.SomeField = tblB.SomeField;

 

[Norah B]

Delete query still doesn't work for me...

Hi - I follow your instructions for building my delete query; when I run it, it asks for a parameter from the field on tbl B where I specified the delete criteria. My query looks like this:

DELETE [Cherise].*
FROM [Cherise] WHERE [RM_TYPE].SESE_ID = [Cherise].SESE_ID;

So I went to design view, pulled in tbl B and linked to the field in tbl A (which is how I've always done this in the past). I receive the following error: "Could not delete from specified tables"

Can you please advise? Thanks very much!!

 

[Pat Hartman]

Take a look at my example again. I fixed it. The original did not include the proper join. Sorry

 

[camedeiros]

still can't delete

Could you please re-explain what it takes to delete the records in table B that match those in table A?

In design view (which I'm familiar), I am showing tables A and B, and joining them (inner join) by Account number. I am then dragging the asterix from table B (where the records that I want to delete are) into a field in the grid. On this field, I should set the "delete" row ro "from", right? And I should not set any criteria, since I want to delet all the matching ones, right?

Could you tell me if I'm doing the right thing and what the next step should be?

Thank you...

 

[Pat Hartman]

You need to change the query type to Delete. You may need to switch to SQL view to verify that the syntax is correct.

 

[Norah B]

Hi -

I made the changes you suggested; I added the inner join, so removed the where clause (as my join was my criteria for deletion). I am still receiving the error 'could not delete from specified tables'.

I tried this in both design and SQL view; my exact syntax is copied below. Can you please advise?

DELETE tblB.*
FROM tblB INNER JOIN [tblA] ON (tblB.SESE_ID = [tblA].SESE_ID) AND (tblB.AGPD_PFX = [tblA].AGPD_PFX);

I've used this exact query to delete data in the past (Access 2000 and earlier) and this is the first time I've had a problem like this - with Access 2002. Is that possibly part of the problem? I've done search on the Microsoft Office help site to find query suggestions, but nothing has contradicted what you've suggested so far or what I've used in the past.

Thanks,
Norah

 

[Pat Hartman]

Make sure both tables have primary keys defined.

 

[Norah B]

I didn't have primary keys set on one of the tables; I made that adjustment and reran the query - still the same error appears.

 

[Norah B]

FYI - I finally got it; I changed from using a join to using a where clause:

DELETE tblB.*
FROM tblB
where AGPD_PFX in (Select distinct AGPD_PFX from [tblA])

I was doing a different type of query altogether and was applying a similar subquery and just thought I'd try it out. Deletion successful!!

Thanks much for all your help...
Norah