DELETE from SELECT statement 1

[MarkTac]

Hi,

I'm writing a code to DELETE all duplicate records. I can easily find all duplicates with SELECT statement. However, I'm confused how to integrate my SELECT into DELETE statement:

SELECT [col1], [col2], [col3] FROM [table1] GROUP BY [col1], [col2], [col3] HAVING (COUNT([col2])>0)

Thank you.

 

[Andrzejek]

Did you try this?

Have fun.

---- Andy

 

[MarkTac]

Well, I have code to remove duplicates using VBA Recordset. However, I was wondering if it is possible by doing SQL DELETE statement.

 

[Andrzejek]

It is easy if you have a unique key in your table.
Do you have a unique key?

Have fun.

---- Andy

 

[MarkTac]

What I came up with so far but I have to run it several times for multiple duplicates of the same record:

DELETE *
FROM [table1]
WHERE ID IN ( SELECT Min(ID) FROM [table1] GROUP BY [col1], [col2], [col3] HAVING COUNT([col2])>1);

 

[dhookom]

How about backing up your database and trying:

DELETE *
FROM [table1]
WHERE ID NOT IN 
(SELECT Max(ID)
 FROM [table1]
 GROUP BY [col1], [col2], [col3]
 HAVING COUNT([col2])>1);

Duane
Hook'D on Access
MS Access MVP

 

[MarkTac]

Works perfectly with a small amount of data. However, when I try to CurrentDb.Execute the statement on large amount of data Access freezes.

 

[dhookom]

I would consider creating a temporary table to append the Max(ID) into. Then create a delete query from table1 and the temp table.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

This should be less slow:

DELETE *
FROM [table1]
WHERE ID NOT IN 
(SELECT Max(ID)
 FROM [table1]
 GROUP BY [col1], [col2], [col3])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

And what about this ?

DELETE * FROM table1 A
WHERE ID <> (SELECT Max(ID) FROM table1
 WHERE col1=A.col1 AND col2=A.col2 AND col3=A.col3)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MarkTac]

Thanks, works perfectly!