Delete Records Based On ID

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Delete Records Based On ID

Post by jstevens »

Is it possible to delete records on two tables who share a common ID?

Example: Delete records where ID = "3456" in both tables. 'Code below is missing the "delete" portion of the code.

Code: Select all

SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete Records Based On ID

Post by HansV »

A delete query can delete records from only one table at a time.
So you could delete all records from Table1 that have a matching ID in Table2.
But after that, Table2 won't have matching IDs in Table1 anymore, so you'd need a trick. Add a Yes/No field ToDelete to Table2.
Execute the following queries, one after another:

UPDATE Table2 SET ToDelete=True WHERE ID In (SELECT ID FROM Table1)

DELETE * FROM Table1 WHERE ID In (SELECT ID FROM Table2)

DELETE * FROM Table2 WHERE ToDelete=True
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Delete Records Based On ID

Post by jstevens »

Thanks Hans!
Regards,
John

CData
3StarLounger
Posts: 308
Joined: 24 Dec 2015, 16:41

Re: Delete Records Based On ID

Post by CData »

there is a Cascade Delete feature in Microsoft Access that is intended to auto delete child records when the parent record is deleted. the tables must have a join relationship. you might want to explore more on this feature.