Delete Field1 records in Table1 when match Field2 in Table2

J

JohS

Hi. This Delete doesn't work, why (I'm getting this error: "Specify Table
containing the Records you want to Delete")?



DELETE Table1.Field1

FROM Table2 LEFT JOIN Table1 ON Table2.Field2 = Table1.Field1

WHERE (((Table1.Field1)<>"False"));



Thanks for help, JohS
 
M

Maurice

Johs,

If you know the criteria you can place it directly in your delete statement
and you don't need the join to table 2 to delete.

Try:
DELETE Table1.Field1 WHERE (((Table1.Field1)<>"False"));

hth
 
J

JohS

I want to delete all the records in Table1 which has it's equals in Table2
(Table1 has only Field1, Table2 has only Field2)
I do get the correct result populated by using:

SELECT Table1.Field1

FROM Table2 LEFT JOIN Table1 ON Table2.Field2 = Table1.Field1

WHERE (((Table1.Field1)<>"False"));



But if I change from SELECT to DELETE, it don't work.
 
J

JohS

OK, found a solution:

DELETE Table1.Field1
FROM Table1
WHERE Table1.Field1 In (SELECT Table1.Field1
FROM Table2 LEFT JOIN Table1 ON Table2.Field2=Table1.Field1
WHERE ((Table1.Field1)<>"False"));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top