CODEWITHSUNDEEP
mysqlsqldatabase
oso9817
oso9817

Reputation: 117

ERR 1288, DB not updatable on deleting duplicate rows

+----+-----------+
| ID | name      |
+----+-----------+
|  1 | fooo      |
|  2 | FWAEFAWEF |
|  3 | test      |
|  4 | test      |
|  5 | test      |
+----+-----------+

This is the database table im using (t1), I'm trying to delete duplicate rows, leaving only one unique ones, I'm trying this in admin using common table expressions

mysql> WITH cte AS (
    -> SELECT
    -> ID,
    -> name,
    -> ROW_NUMBER() OVER (
    -> PARTITION BY
    -> name
    -> ORDER BY
    -> name
    -> ) row_num
    -> FROM
    -> discord.t1
    -> )
    -> DELETE FROM cte
    -> WHERE row_num >1;
ERROR 1288 (HY000): The target table cte of the DELETE is not updatable

Im following a tutorial on deleting duplicate tables so thats where I got the queries from, but im not sure if i should link it

Upvotes: 1

Views: 3127

Answers (2)

forpas
forpas

Reputation: 164089

With a self join:

delete t1
from tablename t1 inner join tablename t2
on t2.name = t1.name and t2.id < t1.id;

See the demo.
Results:

| ID  | name      |
| --- | --------- |
| 1   | fooo      |
| 2   | FWAEFAWEF |
| 3   | test      |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

One method uses aggregation:

delete t1
     from discord.t1 t1 left join
          (select name, min(id) as min_id
           from discord.t1
           group by name
          ) tt
          on t1.id = tt.min_id
    where tt.min_id is null;   -- no match

Upvotes: 1

Related Questions