3

I have a table with the two columns a and b.

I can have data like :

a  | b  
1  | 1  
1  | 3 
1  | 3   
2  | 2   
2  | 3   
2  | 2
2  | 2

In my example, a=2,b=2 and a=1,b=3 are duplicate.

How can I remove all this duplicate from my table?

jarlh
  • 40,041
  • 8
  • 39
  • 58
Clément Andraud
  • 8,707
  • 23
  • 75
  • 149

2 Answers2

4

If only for display use DISTINCT

SELECT DISTINCT *
FROM your_table

Delete from old table (intrusive way), but I suppose you don't have any foreign keys on it. (MySql)

CREATE TABLE new_table AS
SELECT DISTINCT * FROM old_table;

DROP TABLE old_table;

RENAME TABLE new_table TO old_table;

More general way (no need to drop original table):

CREATE TABLE old_table_clone(...);

INSERT INTO old_table_clone(...)
SELECT DISTINCT * FROM old_table;

TRUNCATE TABLE old_table;

INSERT INTO old_table(...)
SELECT * FROM old_table_clone;

DROP TABLE old_table_clone;

The same may be achieved using temp table, dependent on which RDBMS you use like TSQL/SQL Server:

SELECT DISTINCT * INTO #temp FROM old_table;

TRUNCATE TABLE old_table;

INSERT INTO old_table(...)
SELECT * FROM #temp;
Lukasz Szozda
  • 139,860
  • 19
  • 198
  • 228
  • CREATE ... AS SELECT is product specific functionality, please specify which one! – jarlh Sep 08 '15 at 08:11
  • You can also create the table, add a unique index, and insert ignore. – AD7six Sep 08 '15 at 08:22
  • 1
    @jarlh: `create table .. as select ..` is defined in the SQL standard. `select .. into` is non-standard functionality – a_horse_with_no_name Sep 08 '15 at 08:22
  • @a_horse_with_no_name, thanks for correcting. (I tend to forget non-core functionality... Feature T172, “AS subquery clause in table definition”.) – jarlh Sep 08 '15 at 08:48
0

Check: Delete duplicate records using rownum in sql

If you don't have rowid in your database, add unique ID

Community
  • 1
  • 1
li-on
  • 539
  • 2
  • 8