Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 30th, 2006, 08:53 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Checking for duplicates

Hi,

I have to run through a list of tables and check for duplicates. The tables have no primary key setup but we expect that there should be only 1 record for a combination of fields.

So for example if I keep it simple and imagine there is a table called customers with loads of fields and in here the uniqueness of each row is defined by the fields salesrepid and customerid so there should be only 1 occurance of eg salesrep xyz123 and customer abc123

What I need to do is somehow look at the table and check to make sure that the above combination of salesrep xyz123 and customer abc123 only occurs once and if it does then note it / report on it.

Any ideas on how to approach it? I imagine I could have a static table containing the tables to be checked plus the fields of each table that make up the uniquness. So if the tables for checking where in a table called checkthese then in there I could have a field called tablename, and then further rows called pkf1 (primary key field 1), pkf2, pkf3 and so on and for the customers table the pkf1 would have value salesrepid and pkf2 would have value of customerid.

I've probaly not made much sense up there but basically what I need to do is read a table that contains tablenames and fields that make up the uniqness of the table and then go and check those tables to make sure that there are no more than one of each record that makes up the uniqness.

any ideas?

tia,

Ruairi
Derry
Ireland



  #2 (permalink)  
Old May 31st, 2006, 10:39 AM
Registered User
 
Join Date: May 2006
Location: Gurgaon, Haryana, India.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to deepakgnair
Default

To remove the duplicate values in a particular field of a table , u can do the select distinct <field name > into <new table> .......option.To find out the duplicate values, I think, u can join the table to itself(Self join)

  #3 (permalink)  
Old May 31st, 2006, 01:15 PM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT salesrep, customer
FROM customers
group by salesrep, customer
having count(*) > 1

  #4 (permalink)  
Old June 7th, 2006, 03:50 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Or you could cursor through and delete the dupes. This way you don't need to create another table and you could run this while people were using the table.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Checking for Duplicates timeware ASP.NET 2.0 Professional 2 July 20th, 2006 08:41 AM
Yes to duplicates MMJiggy62 Beginning VB 6 3 July 14th, 2006 11:51 AM
Duplicates in XSLT d_sathish XSLT 1 November 9th, 2005 11:45 AM
checking for duplicates jammykam Access 3 October 2nd, 2003 08:06 PM
Duplicates ashley_y Access 11 August 14th, 2003 03:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.