I need to delete duplicate records, but with a specific criteria

[Atenti]

Hi Folks,

I have a query which is pulling some duplicate records from the database. I need the duplicate data to be removed , but i also found out that sometimes one field which is called "URL" has data and when i delete the duplicates i need to keep the record with the URL data. It might be blank for the original record and the duplicated one in which case i only keep one of them , but in the other case when one has URL i need to keep this one.

Can you help me on this? I guess its pretty easy , but i am stuck for the moment :banghead:

Thank You,

Atanas

 

[Big Pat]

Hi,

I think I have a solution. I'm no expert and maybe there are better ways of going about this, but please see the attached database. There are two copies of the table, so you can verify for yourself that the right records have been removed, by comparing both afterwards.

I created 10 records, some of which were duplicated on the CUSTOMER and on the URL and some of which were duplicated only on the CUSTOMER.

It seems to me that you need to approach this in two separate steps. To make it easier to explain (and to keep it straight in my head) I made several numbered queries. If you run each of these in turn you can see the process.

There might be a better way of doing the delete queries themselves. I tried various joins but could not get them to work, so I ended up putting In (Select...From...) in the criteria. I don't know if that is the recommended approach, but for a hack like me it works!

I hope this helps.

Pat.