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
  • 42,561
  • 8
  • 45
  • 63
Clément Andraud
  • 9,103
  • 25
  • 80
  • 158
  • You still want to keep one a=2,b=2 row? – jarlh Sep 08 '15 at 08:05
  • 2
    Remove from table or display (select) without duplicates? – li-on Sep 08 '15 at 08:05
  • Remove from table, and i neek to keep one line each time – Clément Andraud Sep 08 '15 at 08:06
  • 1
    Create a copy table, do INSERT INTO tmp SELECT DISTINCT a,b FROM original_table. – jarlh Sep 08 '15 at 08:11
  • Which DBMS are you using? Postgres? Oracle? –  Sep 08 '15 at 08:23
  • http://stackoverflow.com/q/18932/330315 and http://stackoverflow.com/q/1554060/330315 and http://stackoverflow.com/q/1286843/330315 and http://stackoverflow.com/q/3311903/330315 and http://stackoverflow.com/q/23690867/330315 and http://stackoverflow.com/q/6583916/330315 and http://stackoverflow.com/q/31315856/330315 –  Sep 08 '15 at 08:29

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
  • 162,964
  • 23
  • 234
  • 275
  • 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 –  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