user3392665 user3392665 - 6 months ago 9
MySQL Question

Removing duplicates in MySQL irrespective of column in which the data appears

I have a question very similar to this.

I have a table like so:

Character1      Character2     Age1     Age2     Species     Residence

Donald             Daisy                82        76            Duck       Duck pond

Daisy               Donald              76        82            Duck        Duck pond

Donald             Daisy                82        76            Duck        Duck swamp

Mickey             Minnie              88         88            Mouse     Mouse hole

The rows 1 and 2 are duplicates except for the fact that the values for Character1 & Character2 and Age1 & Age2 are swapped. I want to remove one of these instance and leave the other instance.
To achieve this:

Character1      Character2     Age1     Age2     Species     Residence

Donald             Daisy                82        76            Duck       Duck pond

Donald             Daisy                82        76            Duck        Duck swamp

Mickey             Minnie              88         88            Mouse     Mouse hole

Answer
CREATE TABLE my_table
( a CHAR(1) NOT NULL
, b CHAR(1) NOT NULL
, c CHAR(1) NOT NULL
, PRIMARY KEY(a,b,c)
);
Query OK, 0 rows affected (0.02 sec)

INSERT INTO my_table VALUES
('j','k','x'),
('k','j','x'),
('j','k','y'),
('l','m','x'),
('m','l','x'),
('m','l','z');
Query OK, 6 rows affected (0.01 sec)

SELECT * FROM my_table;
+---+---+---+
| a | b | c |
+---+---+---+
| j | k | x |
| j | k | y |
| k | j | x |
| l | m | x |
| m | l | x |
| m | l | z |
+---+---+---+

SELECT p.* FROM my_table p JOIN my_table q ON q.b = p.a AND q.c = p.c AND p.a > q.a;
+---+---+---+
| a | b | c |
+---+---+---+
| k | j | x |
| m | l | x |
+---+---+---+
2 rows in set (0.00 sec)

DELETE p FROM my_table p JOIN my_table q ON q.b = p.a AND q.c = p.c AND p.a > q.a;
Query OK, 2 rows affected (0.01 sec)

SELECT * FROM my_table;
+---+---+---+
| a | b | c |
+---+---+---+
| j | k | x |
| j | k | y |
| l | m | x |
| m | l | z |
+---+---+---+
4 rows in set (0.00 sec)
Comments