Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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



Reply With Quote
  #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)

Reply With Quote
  #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

Reply With Quote
  #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.



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:02 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.