Ignore/Remove duplicate rows in a column

Question

I have a SQL table:

table

Table code:

CREATE TABLE Gender
(
GenderID int primary key identity,
Gender char(20)
)

I would like to ignore or remove duplicate rows in Gender, whilst maintaining the auto incrementation of GenderID (specified in my create table code), so that it results in:

----------------
|  1  |  Male  |
----------------
|  2  | Female |
----------------

My attempt:

DELETE
FROM Gender
WHERE GenderID NOT IN (
  SELECT MIN(GenderID)
  FROM Gender
  GROUP BY Gender)

Returns: image

And removing duplicates seems as easy as deleting any row where id > 2 and renaming id 2 to 'Female'? Or are you asking how to update foreign key records as well? — Dale K, Apr 24 '20 at 05:04
Attempt is up. Yeah GenderID is a foreign key to another table. — jacob, Apr 24 '20 at 05:15
There is no row in the Gender table that has GenderID = 2 and Gender = 'Female' — KMoe, Apr 24 '20 at 05:21

score 0 · Answer 1 · answered Apr 24 '20 at 05:26

You can create new table and load the values as given below.

DECLARE @datasource TABLE(id int identity(1,1), gender CHAR(10))

INSERT INTO @datasource(gender)
SELECT * FROM 
(
VALUES
('male'),
('male'),
('male'),
('female'),
('female'),
('female')
) as t(gender)

SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT null)) as id, gender 
INTO NewTableName 
FROM @datasource
group by gender

Result Set

+----+-----------+
| id |  gender   |
+----+-----------+
|  1 | female    |
|  2 | male      |
+----+-----------+

score 0 · Answer 2 · answered Apr 24 '20 at 05:34

0

I suggest you drop and re-create the table. Add a unique index with IGNORE_DUP_KEY=ON to prevent duplicates.

[Can I set ignore_dup_key on for a primary key?

answered Apr 24 '20 at 05:34

Ignore/Remove duplicate rows in a column

2 Answers2