Delete record with query and inner join

Question

I want to delete all entries from table PLTotals where the column Total_Row_ID matches the ID column in another table, PLLabelsAndFormulas

**PLTotals** TABLE

ID,       Total_Row_ID,      Member_Row_ID



**PLLabelsAndFormulas** TABLE

ID,       Company_ID,        Row_Label,       etc....

From what I understand I can do it with an INNER JOIN.

First I tried:

Dim accessQuery as string

accessQuery = "DELETE PLTotalsRows.ID FROM PLTotalsRows " & _
              "LEFT JOIN PLLabelsAndFormulas ON PLTotalsRows.Total_Row_ID = PLLabelsAndFormulas.ID " & _
              "WHERE PLLabelsAndFormulas.Company_ID = " & companyId

I get the error:

Need to specify the table to delete from

I also tried:

accessQuery = "DELETE PLTotalsRows.* FROM PLTotalsRows WHERE " & _
              "EXISTS (SELECT [ID] FROM PLLabelsAndFormulas WHERE PLLabelsAndFormulas.ID = PLTotalsRows.Member_Row_ID) = True"

This doesn't give an error, but the records aren't deleted.

Answer 1

Consider using an alias to correlate outer level with EXISTS subquery.

DELETE t.* 
FROM PLTotalsRows t
WHERE EXISTS (
    SELECT 1 
    FROM PLLabelsAndFormulas 
    WHERE PLLabelsAndFormulas.ID = t.Member_Row_ID
)

Alternatively, you can use IN subquery:

DELETE t.* 
FROM PLTotalsRows t
WHERE t.Member_Row_ID IN (
    SELECT [ID] 
    FROM PLLabelsAndFormulas 
)