CODEWITHSUNDEEP
sqldatabasems-access
Mohd akhtar
Mohd akhtar

Reputation: 55

What's wrong with my DELETE query

I've two tables with same columns. I'm trying to delete rows from table1 '600_LONDON_NUMBER' which are in table2 '600_LONDON_NUMBER1'. Below is my query but when I run it, MS Access says "Could not delete from the specified Tables". Please Help

DELETE [600_LONDON_NUMBER].*
FROM 600_LONDON_NUMBER INNER JOIN 600_LONDON_NUMBER1 
ON ([600_LONDON_NUMBER].GFCID = [600_LONDON_NUMBER1].GFCID) AND ([600_LONDON_NUMBER].CUSTBaseNO = [600_LONDON_NUMBER1].[CUST Base NO]);

P.S. When I run the SELECT Statement for the same query, it retrives the data without any Issue. I've also checked that data is not readonly I can delete using simple DELETEquery.

Upvotes: 0

Views: 53

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

you may use EXISTS to solve it

DELETE 600_LONDON_NUMBER.* FROM 600_LONDON_NUMBER 
WHERE EXISTS (
     SELECT 1 FROM 600_LONDON_NUMBER1 
     WHERE [600_LONDON_NUMBER].GFCID = [600_LONDON_NUMBER1].GFCID) AND 
           [600_LONDON_NUMBER].CUSTBaseNO = [600_LONDON_NUMBER1].[CUSTBaseNO]
)

Upvotes: 1

Related Questions