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??
Delete duplicate records from a table having composite primary key & a condition(10 posts) (4 voices)
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' go
let me know if you have any doubt on it.
Hi.. i tried this is not working.. probably your it will copy the content of one table to another table..
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 > 1
This will do the operation without tampering the structure of the table or database.
For more information, visit the below links:
thanks Arun. it is working.. but i found another alternative.. so thought to post here..
Push all the required data into a temporary table ( insert * into #table_temp from table_orig where ...... )
Then truncate table_orig
Push all data from the temporary table to the origanal table.
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..
can u pls post the exact query that you used to push records to a temp table.
I 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.
@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.
sure.. i will update the query.. by eod tommorrow..
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:
Do some insert or delete operation and after that run the above statement to see the log entries.