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
Solved

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

Posted on 2007-11-27
10
259 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

840 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