Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-27
10
Medium Priority
?
263 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

722 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