What's wrong with my DELETE query

Question

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.

@FlorianKoch `MsAccess` can have that **part**. see this https://msdn.microsoft.com/en-us/library/bb177896(v=office.12).aspx — Sankar, Sep 27 '17 at 07:13
@FlorianKoch Tried removing it, now it says **"Specify the table containing the records you want to delete"** — Mohd akhtar, Sep 27 '17 at 07:13
Possible duplicate of [Delete with Join in MySQL](https://stackoverflow.com/questions/652770/delete-with-join-in-mysql) — GolezTrol, Sep 27 '17 at 07:17
Ok, that's what you get when you go rogue on the tags. Now you got a bunch of MySQL answers and comments that are useless to you. — GolezTrol, Sep 27 '17 at 07:18

score 1 · Accepted Answer · answered Sep 27 '17 at 07:15

1

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]
)

answered Sep 27 '17 at 07:15

Radim Bača

10,646
1
19
33

What's that 1 representing in SELECT 1? – Mohd akhtar Sep 27 '17 at 07:21
`EXISTS` operator returns true, if `SELECT` return at least something, therefore, scalar value 1 is that something. – Radim Bača Sep 27 '17 at 07:25
Thanks, I just had to add the correct column name... below works.. `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].[CUST Base NO]);` – Mohd akhtar Sep 27 '17 at 07:28

What's wrong with my DELETE query

1 Answers1