MySQL
SQL

MySQLで重複する行を削除する

More than 1 year has passed since last update.


ケース1:id以外の全カラムが一致する行があるとき、1個を残して削除する方法

例:

+----+-----------+-------+

| id | name | price |
+----+-----------+-------+
| 1 | りんご | 100 |
| 2 | りんご | 200 |
| 3 | バナナ | 250 |
| 4 | バナナ | 250 | ←削除したい
| 5 | ぶどう | 100 |
| 6 | ぶどう | 100 | ←削除したい
| 7 | ぶどう | 120 |
+----+-----------+-------+

削除するSQL:

DELETE FROM fruits WHERE id NOT IN (SELECT min_id from (SELECT MIN(id) min_id FROM fruits GROUP BY name, price) tmp);

実行後:

+----+-----------+-------+

| id | name | price |
+----+-----------+-------+
| 1 | りんご | 100 |
| 2 | りんご | 200 |
| 3 | バナナ | 250 |
| 5 | ぶどう | 100 |
| 7 | ぶどう | 120 |
+----+-----------+-------+


ケース2:全てのカラムが一致する行があるとき、1個を除いて削除する方法

例:

+-----------+-------+

| name | price |
+-----------+-------+
| りんご | 100 |
| バナナ | 200 |
| りんご | 100 | ←削除したい
| ぶどう | 300 |
| ぶどう | 300 | ←削除したい
| ぶどう | 300 | ←削除したい
+-----------+-------+

create table fruits2 (name varchar(100), price int);
insert into fruits2 (name, price) values ('りんご', 100);
insert into fruits2 (name, price) values ('バナナ', 200);
insert into fruits2 (name, price) values ('りんご', 100);
insert into fruits2 (name, price) values ('ぶどう', 300);
insert into fruits2 (name, price) values ('ぶどう', 300);
insert into fruits2 (name, price) values ('ぶどう', 300);

テンポラリテーブルとSELECT DISTINCTを使うのがいいかもしれない。

削除するSQL:

CREATE TEMPORARY TABLE fruits2_tmp AS SELECT DISTINCT * FROM fruits2;

DELETE FROM fruits2;
INSERT INTO fruits2 SELECT * FROM fruits2_tmp;
DROP TABLE fruits2_tmp;

実行後:

+-----------+-------+

| name | price |
+-----------+-------+
| りんご | 100 |
| バナナ | 200 |
| ぶどう | 300 |
+-----------+-------+

もっといいやり方があったら教えて下さい。

OracleだとROWIDを使ってケース1と同じやり方ができるのだけど。


ケース3:指定カラムが重複する行を削除する場合

例:priceが重複する行を削除したい

+------------+-------+

| name | price |
+------------+-------+
| りんご1 | 100 |
| りんご2 | 100 | ←削除したい
| りんご3 | 200 |
| りんご4 | 200 | ←削除したい
| りんご5 | 200 | ←削除したい
+------------+-------+

create table fruits3 (name varchar(100), price int);
insert into fruits3 (name, price) values ('りんご1', 100);
insert into fruits3 (name, price) values ('りんご2', 100);
insert into fruits3 (name, price) values ('りんご3', 200);
insert into fruits3 (name, price) values ('りんご4', 200);
insert into fruits3 (name, price) values ('りんご5', 200);

テンポラリテーブルとSELECT GROUP BYを使えばできる。

削除するSQL:

CREATE TEMPORARY TABLE fruits3_tmp AS SELECT MIN(name), price FROM fruits3 GROUP BY price;

DELETE FROM fruits3;
INSERT INTO fruits3 SELECT * FROM fruits3_tmp;
DROP TABLE fruits3_tmp;

実行後:

+------------+-------+

| name | price |
+------------+-------+
| りんご1 | 100 |
| りんご3 | 200 |
+------------+-------+