Solved

SQL Server, 2000 - How to delete duplicated rows if the primary key is multiple columns

Posted on 2007-11-27
10
262 Views
Last Modified: 2010-04-21
Experts,
I have got a table “Trade” that has a combined primary key “Deal_Number,Order_Number,ValueDate”
While setting the primary key, the table already had data, which was duplicated for some dates.
How may I execute a statement that deletes all duplicates?
Note: I need to delete extra duplicates only and not all data. i.e. if rows of 01/12/2000 are duplicated 3 times, I need to delete them 2 times and keep 1 instance of them.

Thanks in advance,
0
Comment
Question by:feesu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20363786
For this the best way to do is Create another table with same structure along with primary key on the column you want and then Select DISTINCT Rows from the first table and insert them into the new table.

This is the only way you can do this.
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20363797
Or directly do a Select DISTINCT from the table into new table name and then drop the source and rename the new table to old table name. for example

Select Distinct Deail_Number, Order_Number, ValueDate, Col4, Col5,... Into NewTableName from DuplicatedValueTableName.

Drop the DuplicatedValueTableName

and then rename the NewTableName to OldTableName
0
 

Author Comment

by:feesu
ID: 20364105
But what if i have 10 columns, and the primary key (the ones i need to distinctly select) are only 3 ???
How do i write my select statement?

-----------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT     IKey, Deal_Number, Deal_Time, Order_Number, Order_Time, Order_Type, CompanyID, Price, QTY, Investor_Number, [User], Status, 
                      Main_Forward_Market_Deal_Date, Main_Deal_Number, Investment_Company_Number, ValueDate
FROM         Broker_TRADE
-----------------------------------------------------------------------------------------------------------------------------------------------------------

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20364171
SELECT   Min(IKey), Deal_Number, Min(Deal_Time), Order_Number, Order_Time, Order_Type, CompanyID, Price, QTY, Investor_Number, [User], Status,
                      Main_Forward_Market_Deal_Date, Main_Deal_Number, Investment_Company_Number, ValueDate
FROM         Broker_TRADE
group by Deal_Number, Order_Number, ValueDate

Please add the Min or Max function as i have used according to what you need. The query will go with group by your distinct columns. Dont put min or max to your group by cols.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20364512
how big is the table, how many rows?
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20364587
this T-SQL should do it. as with any advice, back up your database/table first!
set nocount on
 
declare @count int
declare @dealnumber int
declare @ordernumber varchar(100)
declare @valuedate datetime
select @count = 1
while @count > 0
begin
 set @count = 0
 select top 1
	@dealnumber = Deal_Number,
	@ordernumber = Order_Number,
	@valuedate = ValueDate,
	@count = count(*)
 from Broker_TRADE
 group by Deal_Number, Order_Number, ValueDate
 having count(*) > 1
 order by Deal_Number, Order_Number, ValueDate
 
 if @count > 0
 begin
  set rowcount @count
  delete from Broker_TRADE
  where Deal_Number = @dealnumber
    and Order_Number = @ordernumber
    and ValueDate =@valuedate
  set rowcount 0
 end
end
 
set nocount off

Open in new window

0
 

Author Comment

by:feesu
ID: 20364605
Hi imitchie,
The table has around 100,000 rows.
It has the columns: IKey, Deal_Number, Deal_Time, Order_Number, Order_Time, Order_Type, CompanyID, Price, QTY, Investor_Number, [User], Status, Main_Forward_Market_Deal_Date, Main_Deal_Number, Investment_Company_Number, ValueDate

Do I execute your code now?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20364684
yup, that'd do it
0
 

Author Comment

by:feesu
ID: 20364814
Hi imitchie!!
It worked perfectly!! Thank you very much. This was a relief :)
0
 

Author Closing Comment

by:feesu
ID: 31411367
PERFECT!!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question