By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Deleting Duplicate records with different criterias?

matrekz42
P: 33
Hello Guru's

(DELETE QUERY)

I have a table that has an account number field and a dollar amount associated with that field. I want to delete one of the fields only, if the dollar amount is the same, and keep the other account number field. How can I go about this, I've tried the following, but it deletes both and it's only for the account number...

In (SELECT [account#] FROM [Table1] As Tmp GROUP BY [account#] ] HAVING Count(*)>1 )

All help is greatly appreciated.
Apr 17 '08 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, matrekz.

Take a look at Delete Similar Records

Regards,
Fish
Apr 17 '08 #2

matrekz42
P: 33
Hi Fish,

Thank you for your response, I tried those scenarios with no success, is there a way that I can change a value to one of the duplicate records for example:

I have a table with an account ID and there is a $ amount associated to that ID, but now I have two duplicate ID's with the same values but for different dates.

So I want to keep both but maybe change the value to the latest record, I dont know if an (IIF) statement would be better for this?
Apr 18 '08 #3

FishVal
Expert 2.5K+
P: 2,653
I want to delete one of the fields only, if the dollar amount is the same, and keep the other account number field.
So I want to keep both but maybe change the value to the latest record, I dont know if an (IIF) statement would be better for this?
))))

So, what is relevant so far?
You should explain the logic very clearly. If you want to delete one (or all but one) record, then what criteria should be used to distinguish among the duplicates?

Also, it would be helpful to get some idea about the dataset mentioned. Is that query or table? What is query/table SQL/metadata?

Regards,
Fish
Apr 18 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.