Delete dup ID's with empty comments

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:
I have pulled together 3 distinct tables using a union query into a single
table. These tables do not have keys. They DO have ID's. The ID's can be
duplicates because of the nature of the data sources. There are only 2
fields: ID, comments. I want to delete the rows where the Count(ID>1) AND
comments is null/" ". I have created queries to identify these rows, but am
not successful in getting the delete query to actually delete them.

How can this be done in a single delete query using only the single
(union-ized) underlying table??

Much appreciated~
 
Did you create a new table and populate it with the results from the UNION
query? And do you want to delete the duplicates from the new table?

Or are you trying to delete from the three tables? If so, there is no way to
delete from three tables with one delete query.

And when you delete the duplicates, do you want to delete ALL of them in all
three tables or just all but one of them. In other words your criteria aren't
very clear to me.

Can you post the SQL statements you used to populate your combined table? And
be a bit more specific on whether you want to delete ALL the records in ALL the
tables if your criteria is met?
 
Hi John:
yes, I mentioned "single unionized table". to delete the empty duplicate
rows from the single unionized table.
 
DELETE YourTable.*
FROM YourTable
WHERE ID IN
(SELECT ID
FROM YourTable
GROUP BY ID
HAVING Count(id) >1)
AND (Comments Is Null Or Comments = "")

That will delete ALL the duplicates that have comments that are null or a
zero-length string.

Is that what you want?
 
Back
Top