For every table, we tend to use a primary key or distinctive key for distinct values, however generally we'll encounter a problem with duplicate records. it's a significant issue with duplicate rows. thus we want to delete duplicate records to resolve it. So here i'll make an example to explain delete duplicate rows in MSSQL Hosting.
Example:
First we create a simple database with one table for membership that contains: a Member's ID, Membership type, Firstname, Lastname and Birthday:
CREATE TABLE [dbo].[Membership](
[MemberID] [varchar](20) NOT NULL,
[MemberType] [int] NOT NULL,
[Firstname] [varchar](80) NOT NULL,
[Lastname] [varchar](80) NOT NULL,
[Birthday] [date] NOT NULL
) ON [PRIMARY]
Now, insert the following rows into this table:
INSERT INTO dbo.Membership (MemberID, Firstname, Lastname, MemberType, Birthday) VALUES
('001', 'Peter', 'Smith', 1, convert(datetime, '05/23/1988', 0))
INSERT INTO dbo.Membership (MemberID, Firstname, Lastname, MemberType, Birthday) VALUES
('002', 'Scott', 'Davidson', 1, convert(datetime, '07/10/1983', 0))
INSERT INTO dbo.Membership (MemberID, Firstname, Lastname, MemberType, Birthday) VALUES
('002', 'Scott', 'Davidson', 1, convert(datetime, '07/10/1983', 0))
INSERT INTO dbo.Membership (MemberID, Firstname, Lastname, MemberType, Birthday) VALUES
('003', 'Kevin', 'Richard', 1, convert(datetime, '04/24/1985', 0))
We currently have duplicated Scott Davidson’s entry (MemberID 2), with no clearly safe method of removing the duplicate, whereas leaving at least one entry intact. Now, imagine if you had a complete databse of information with a whole lot or thousands of duplicates. Fixing this by hand quickly becomes an impossible task!
To solve this, first we can add an column using the IDENTITY keyword so we currently have a unique method of addressing every row:
ALTER TABLE dbo.Membership ADD AUTOID INT IDENTITY(1,1)
We can then we can use:
SELECT * FROM dbo.Membership WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.Membership GROUP BY MemberID)
Which will properly choose all duplicated records in our database (always worth checking before running the delete query!). Once we are happy this is working for us, we can then run the delete query:
DELETE FROM dbo.Membership WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.Membership GROUP BY MemberID)