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

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,
feesuAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
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
 
Ashish PatelCommented:
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
 
Ashish PatelCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
feesuAuthor Commented:
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
 
Ashish PatelCommented:
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
 
imitchieCommented:
how big is the table, how many rows?
0
 
feesuAuthor Commented:
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
 
imitchieCommented:
yup, that'd do it
0
 
feesuAuthor Commented:
Hi imitchie!!
It worked perfectly!! Thank you very much. This was a relief :)
0
 
feesuAuthor Commented:
PERFECT!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.