Hi,
I have to delete the duplicate records from table ABC where col1,col2,col3 is my composite primary key. also another column reject_key is there in the table. I have to retain those records having max value of reject_key. Can you please help me out??
Thanks
Sachin.
Delete duplicate records from a table having composite primary key & a condition
(10 posts) (4 voices)-
Posted 2 years ago #
-
thomasb4all
MemberHi sachin
why don't u use "Group By" or "distinct" keywords and replace your redundant table with the unique one.
Use the below query to get it done:select col1, col2, col3 into ABCtemp from ABC group by col1, col2, col3;go; drop table dbo.ABC go; sp_rename 'ABCtemp', 'ABC' golet me know if you have any doubt on it.
Posted 2 years ago # -
Hi
I hope Sachin does not want to fiddle with the database structure.
Anyhow, i have an alternative solution for this, by using ranking functions i.e. ROW_NUMBER()Use the below Query:
WITH CTE as ( select ROW_NUMBER() over(partition by col1, col2, col3 order by col1) as RecID, * from dbo.ABC ) DELETE from CTE where RecID > 1This will do the operation without tampering the structure of the table or database.
For more information, visit the below links:http://www.sqllion.com/2009/05/delete-duplicate-records/
http://www.sqllion.com/2010/07/row_number/
Thanks,
ArunPosted 2 years ago # -
thanks Arun. it is working.. but i found another alternative.. so thought to post here..
Step 1
Push all the required data into a temporary table ( insert * into #table_temp from table_orig where ...... )
Step 2
Then truncate table_orig
Step 3
Push all data from the temporary table to the origanal table.
Step 4
Drop the truncate table-------------
This will create a single record for each transaction in the log file.. in stead of creating a record for each row...Can any one suggest anything..
Posted 2 years ago # -
abousa
MemberI guess snt_cool has a good point.
As truncating a table will not create massive log entries as compare to deleting records.It will just deallocates the data pages used by the table keeping only one transaction log for page deallocation. But it bypasses the transaction log, its almost impossible to recover data once it is truncated.
Posted 2 years ago # -
thomasb4all
Member@abousa, its true that truncate table deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. And thus, truncate table cannot be rolled back.
Although, its a good idea to truncate table instead of deleting records in the above problem, but if you go for truncating, you need to insert the data once again to the main table which indeed create logs for each record.
Posted 2 years ago # -
Hi sachin,
One thing you said correct that by truncating you are saved from Log entries.
But in your above method you are inserting the unique records from the temp table to the main table i.e. ABC, resulting in creation of Log entries in your database.
I am agree with the "Thomasb4all" comment.If logs needs to be considered as a first priority then, i would suggest you to check for the below points:
• the no. of records you are deleting Vs no. of records you are inserting (if the no. of records you are deleting is much less then no. of records you are going to insert in the alternate method, then its always better to delete with the method i suggested earlier.• Either in deleting or inserting in both the cases there will be Log entries.
• Creating #temp tables and inserting data into it will not create any Log entries, you can drop them later on.
• The constraints associated with your main table, if there is any PK-FK relationship with other tables then you may not truncate the table.
• Check for the easiest way to accomplish your task i.e. by ROW_NUMBER and CTE / or by truncating table with distinct or group by.
Moreover, you can check the transaction logs getting affected by the below query:
dbcc log('TestDB')Do some insert or delete operation and after that run the above statement to see the log entries.
Thanks,
ArunPosted 2 years ago #
