CODEWITHSUNDEEP
mysql
user3392665
user3392665

Reputation: 29

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

Upvotes: 0

Views: 40

Answers (1)

Strawberry
Strawberry

Reputation: 33935

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.a = p.b
   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.a = p.b
   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)

Upvotes: 2

Related Questions