Removing same entries in MySQL table

Question

I am a beginner in MySQL. Here I had just created a table

CREATE TABLE Student (
    Rollno int,
    LastName varchar(255),
    FirstName varchar(255),
    Gpa float,
);

Once after creating the table I had added the values with the help of the below query

USE persons;
INSERT INTO student
VALUES
 (1, 'Aa', 'A', 8.5),
 (2, 'Bb', 'B', 8.6),
 (3, 'Cc', 'C', 8.7),
 (4, 'Dd', 'D', 8.4);

I had just run the above query twice which resulted in having two entries as shown below

So I just wanted to remove the duplicate entries and the resultant database should resemble as

Though I searched and tried I don't possess any unique values like id mentioned in this answer.

And again I tried with the help of this answer the code is shown below

WITH cte AS (
    SELECT
        Rollno,
        LastName,
        FirstName,
        Gpa,
        ROW_NUMBER() OVER (
            PARTITION BY
                Rollno
            ORDER BY
                Rollno
        )RN
     FROM
        persons.student
)
DELETE FROM cte
WHERE RN > 1;

but the above throws error stating

Error Code:1288. The target table cte of the DELETE is not updatable

So I just wanted an optimal solution to have single entries in the table.

Use LIMIT for delete statement. See answer at: [https://stackoverflow.com/questions/12779978/delete-first-x-lines-of-a-database](https://stackoverflow.com/questions/12779978/delete-first-x-lines-of-a-database) — Daniel111, Apr 07 '21 at 17:33
Drop and re-create the table with rollno assigned as the primary key. Then re-do the inserts — Strawberry, Apr 07 '21 at 17:56

Bill Karwin · Accepted Answer · 2021-04-07T18:39:25.103

Here's a solution I tested using your data. Thank you for providing a case that was complete and easy to test!

mysql> alter table student add column id int auto_increment primary key first;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+----------+-----------+------+
| id | Rollno | LastName | FirstName | Gpa  |
+----+--------+----------+-----------+------+
|  1 |      1 | Aa       | A         |  8.5 |
|  2 |      2 | Bb       | B         |  8.6 |
|  3 |      3 | Cc       | C         |  8.7 |
|  4 |      4 | Dd       | D         |  8.4 |
|  5 |      1 | Aa       | A         |  8.5 |
|  6 |      2 | Bb       | B         |  8.6 |
|  7 |      3 | Cc       | C         |  8.7 |
|  8 |      4 | Dd       | D         |  8.4 |
+----+--------+----------+-----------+------+
8 rows in set (0.00 sec)

mysql> delete s1 from student as s1 join student as s2 on s1.rollno=s2.rollno and s1.id > s2.id;
Query OK, 4 rows affected (0.03 sec)

mysql> select * from student;
+----+--------+----------+-----------+------+
| id | Rollno | LastName | FirstName | Gpa  |
+----+--------+----------+-----------+------+
|  1 |      1 | Aa       | A         |  8.5 |
|  2 |      2 | Bb       | B         |  8.6 |
|  3 |      3 | Cc       | C         |  8.7 |
|  4 |      4 | Dd       | D         |  8.4 |
+----+--------+----------+-----------+------+
4 rows in set (0.00 sec)

Re your comment:

The s1 and s2 in this query are common called "table aliases" but you should think of them more like row aliases. They reference one row at a time, so you can compare the values in the respective rows.

Suppose there are two rows like this:

+----+--------+----------+-----------+------+
| id | Rollno | LastName | FirstName | Gpa  |
+----+--------+----------+-----------+------+
|  1 |      1 | Aa       | A         |  8.5 |
|  5 |      1 | Aa       | A         |  8.5 |

Which row can be indicated by s1 and which row can be s2 such that the join condition is true?

The two rows have the same Rollno, so the join condition s1.rollno=s2.rollno is satisfied either way.

One row has an id with a lesser value than the other, so for the join condition s1.id > s2.id to be satisfied, we know that s2 is the first row with id 1, and s1 is the second row with id 5.

Thus the two terms of the join condition together guarantee that s1 and s2 will reference two rows with the same Rollno, and s2 will have a lesser id value.

+----+--------+----------+-----------+------+
| id | Rollno | LastName | FirstName | Gpa  |
+----+--------+----------+-----------+------+
|  1 |      1 | Aa       | A         |  8.5 | <-- s2
|  5 |      1 | Aa       | A         |  8.5 | <-- s1

We want to keep the "first" row with the lesser id value (s2), and delete the other one (s1). Hence the delete s1 from .... In MySQL's multi-table delete syntax, you specify which row will be deleted before the from keyword. The other row alias defined in the from clause (s2) will not be deleted.

Could I know more exactly on how `s1.id > s2.id` works. – Krithick S Apr 07 '21 at 18:29 — Krithick S, Apr 07 '21 at 18:29

Removing same entries in MySQL table

1 Answers1