feesu
asked on
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,Or der_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,
I have got a table “Tradeâ€Ã
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,
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
Select Distinct Deail_Number, Order_Number, ValueDate, Col4, Col5,... Into NewTableName from DuplicatedValueTableName.
Drop the DuplicatedValueTableName
and then rename the NewTableName to OldTableName
ASKER
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?
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
-----------------------------------------------------------------------------------------------------------------------------------------------------------
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_D ate, 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.
Main_Forward_Market_Deal_D
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.
how big is the table, how many rows?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_D ate, Main_Deal_Number, Investment_Company_Number, ValueDate
Do I execute your code now?
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_D
Do I execute your code now?
yup, that'd do it
ASKER
Hi imitchie!!
It worked perfectly!! Thank you very much. This was a relief :)
It worked perfectly!! Thank you very much. This was a relief :)
ASKER
PERFECT!!
This is the only way you can do this.