remove duplicate values of one column by sql query

Question

I have a table with two column, values of one column is null and another column have many duplicated values and blanks. how can I remove duplicated values and blanks from that column by a query?

Is this a one-shot operation or are you going to "clean" your table regularly? — Chris Tophski, Sep 08 '16 at 12:21

score 2 · Accepted Answer · answered Sep 08 '16 at 13:17

2

You can use temporary table for this task as below:

SELECT DISTINCT * INTO #tmpTable FROM MyTable
TRUNCATE TABLE MyTable
INSERT INTO MyTable SELECT * FROM #tmpTable

answered Sep 08 '16 at 13:17

Shushil Bohara

5,556
2
15
32

score 1 · Answer 2 · edited May 23 '17 at 12:33

1) If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

2) If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

Please get the copy of your table before processing this. if don't get than send me table name i will put replace your table name.

It is tested with MySQL 5.1

Not sure about other versions see the link

Delete all Duplicate Rows except for One in MySQL?

score 0 · Answer 3 · answered Sep 08 '16 at 12:39

0

You can create an empty copy of the table. Then you run an INSERT INTO new_table SELECT DISTINCT * FROM old_table. Finally, drop the old table and rename the new one.

answered Sep 08 '16 at 12:39

Chris Tophski

930
1
6
23

score 0 · Answer 4 · answered Sep 08 '16 at 12:49

One way of doing this - using CTE

create table #dups (col1 int, col2 int)
insert into #dups
select null,null union all
select null,1 union all
select null,1 union all
select null,1 union all
select null,2 union all
select null,2 union all
select null,3 union all
select null,null
select * from #dups
;WITH cte
     AS (SELECT col1,col2,ROW_NUMBER() OVER (PARTITION BY  Col1,Col2 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #dups
        )
DELETE FROM cte
WHERE  RN > 1 OR col2 is null

score 0 · Answer 5 · answered Sep 08 '16 at 13:40

0

If I understood right, you want to avoid for the column2 NULL and blanks?

SELECT COLUMN1, COLUMN2 FROM TABLE 
GROUP BY
  COLUMN1, COLUMN2 
WHERE
  COLUMN2 NOT NULL AND COULMN2 <> ''

This query is going to show results only when the COLUMN2 has some data.

answered Sep 08 '16 at 13:40

Pedro Augusto Freitas de Souza

952
7
17

score 0 · Answer 6 · answered Sep 08 '16 at 16:40

try this

DELETE FROM [Table]
WHERE     (ColmnB IN
                      (SELECT     ColumnB
                         FROM         [Table] AS Table_1
                         GROUP BY ColumnB
                         HAVING      (COUNT(*) > 1))) OR
                  (RTRIM(LTRIM(ISNULL(ColumnB,''))) = '')

table has tow column . first column is null value . second column has duplicate value and blank value.

remove duplicate values of one column by sql query

6 Answers6