Solved

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

Posted on 2007-11-27
10
255 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
  • 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
 
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
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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now