MS Access How to convert this select query to Delete query?

Question

I have the following select query

SELECT t1.*
FROM t1, t2
WHERE t1.field1=t2.field1 And t1.field2=t2.field2 And t1.field3=t2.field3 ;

I want to convert this into a delete query. how should i write it?

Answer 1

Not 100% on access but does:

DELETE t1
FROM t1, t2
WHERE t1.field1=t2.field1 And t1.field2=t2.field2 And t1.field3=t2.field3 ;

Work?

Answer 2

Try this

DELETE FROM t1
FROM t1 AS tt1, t2 AS tt2
WHERE tt1.field1=tt2.field1 And tt1.field2=tt2.field2 And tt1.field3=tt2.field3 ;

EDIT:

Did this in MS Access

DELETE DISTINCTROW t1.*
    FROM t1 INNER JOIN t2 ON (t1.field3 = t2.field3) AND (t1.field2 = t2.field2) AND (t1.field1 = t2.field1);

And it worked, you have to set the Unique Records to Yes

Answer 3

What about this:

DELETE 
FROM t1
INNER JOIN t2 ON t1.field1=t2.field1 
  And t1.field2=t2.field2 
  And t1.field3=t2.field3

Will delete all records in t1 that have a matching record in t2 based on the three field values.

Answer 4

What about this query:

DELETE FROM t1
WHERE t1.field1 IN (
  SELECT t1.field1 FROM t1, t2  
  WHERE t1.field1=t2.field1 And
        t1.field2=t2.field2 And
        t1.field3=t2.field3)