Reputation: 486
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.
Upvotes: 2
Views: 141
Reputation: 563021
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.
Upvotes: 1