SQL SERVER - Delete Duplicate Records - Rows
Wednesday, August 8, 2007
Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateValueColumn1, DuplicateValueColumn2 and DuplicateValueColumn3.
DELETE
FROM MyTable
WHERE ID NOT IN
(SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicatevalueColumn1, DuplicateValueColumn2, DuplicateValueColumn3
OR
Delete from table t1
where t1.rowid>(select from table t2
from table t2
where t1.col_name=t2.col_name);
OR
delete from emp where rowid in(
select rowid from (
select emp_no, sal,rownum() over (emp_no,sal) as
Rownum from emp order_by emp_no,sal
) where rownum > 2
)
Bonus Q:
John writes "I'd like to be able to automatically number each row as it comes into the table. Is possible to do this? How do I know what the value of the row I just inserted is?"
Well John,
What you can do is set up an identity column in SQL Server. This will autonumber the rows. In Enterprise Manager, start by setting a column with a datatype of int. This column cannot allow nulls. Check the Identity checkbox and the Identity Seed and Identity Increment will be set to 1 automatically. These do just what they say they will. The Identity Seed is the value of the first entry in the table. The Identity Increment is the value that will be added to the previous row to get the next identity value.
Note that if you delete all the rows in a table and start adding rows, the identity column will pick up where it left off. You can reset it but that's a topic for another day.
The script to create a table looks like this:
CREATE TABLE [dbo].[Items] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemName] [char] (50) NOT NULL
)
You can use the @@IDENTITY function to return the value you just inserted.
posted by MIGHTYMAK @ 12:01 PM,
