CODEWITHSUNDEEP
ms-access
tksy
tksy

Reputation: 3529

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

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?

Upvotes: 1

Views: 2072

Answers (5)

Krebiozen
Krebiozen

Reputation: 1

I've been struggling with something similar.

I found the easiest way is not to use a query at all, but to create an empty duplicate table with multiple Primary Keys with Duplicates OK set (Design View hold down Ctrl key and select the rows you want and then right click and select them all as Primary Keys).

Then copy and paste all the rows from your table into the new table. OK the error messages and you will find you have a table with only unique values in the fields you wanted.

That has the additional benefit of not allowing duplicate rows in your new table.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

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

Upvotes: 1

Nick Dandoulakis
Nick Dandoulakis

Reputation: 43110

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)

Upvotes: 3

patmortech
patmortech

Reputation: 10219

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.

Upvotes: 0

Preet Sangha
Preet Sangha

Reputation: 65496

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?

Upvotes: 1

Related Questions