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

Remove Duplicates from Table

natalie99
P: 41
hi everyone

i have reseached this topic and cannot seem to find a solution that suits my problem, please help!!

I have two tables, "Inventory" which contains 30,000 or so records, with about 2000 duplicates. Each duplicate contains the exact same data in every field except one, Field "Code", which may have "Agreed" or "Rejected".

The second table, "Duplicate Coding" contains the correct field value for "Code" for the records which are duplicates. This table was created by using Find Duplicates and then running a query to negate the incorrect Code values.

Now I need to merge the data, I wish to delelte all duplicate lines where the Code value from Duplicate Coding does not match, WITHOUT affecting the other lines in table Inventory.

The two tables both contain an ID number to reference records.

I hope this makes sense? I know there will be a simple solution but I can't seem to make it work!

Thanks in advance!
Jul 1 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,610
Kindly post some data to visually illustrate your request.
Jul 1 '08 #2

puppydogbuddy
Expert 100+
P: 1,923
Expand|Select|Wrap|Line Numbers
  1. update tblInventory
  2.     set tblInventory.Code = tblDuplicates.Code
  3.     where tblInventory.ID = tblDuplicates.ID
Jul 1 '08 #3

NeoPa
Expert Mod 15k+
P: 31,277
Try the following query. Let me know if it complains about using a non-updatable query to delete.
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM tblInventory AS tI INNER JOIN
  3.      [Duplicate Coding] AS tDC
  4.   ON tI.ID=tDC.ID
  5.  AND tI.Code<>tDC.Code
Jul 2 '08 #4

NeoPa
Expert Mod 15k+
P: 31,277
PS. Please remember to title your questions correctly in future ;)
Jul 2 '08 #5

natalie99
P: 41
Thank you everyone :)

I ended up using this format:
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM tblInventory AS tI INNER JOIN
  3.      [Duplicate Coding] AS tDC
  4.   ON tI.ID=tDC.ID
  5.  AND tI.Code<>tDC.Code
with a small change, as this exact query timed out every time I tried to run it. Thank you NeoPa for helping me fix my shoddy data!!

Cheers

Nat
Jul 23 '08 #6

NeoPa
Expert Mod 15k+
P: 31,277
No worries Nat.

I'd be interested to see exactly what SQL code you did use mind you, to see where in my code I could have done it differently perhaps.
Jul 28 '08 #7

natalie99
P: 41
:S

So very sorry I should have stated that correctly, changes were only due to naming conventions, not coding issues!

thanks for your help (again!)
Aug 5 '08 #8

NeoPa
Expert Mod 15k+
P: 31,277
You're sorted now Nat, that's the important thing :)
Aug 5 '08 #9

Post your reply

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