Sebastianb Sebastianb - 5 months ago 7
MySQL Question

Limit ammount of rows deleted in table without PK

I'm working on a system where, for some reason, someone created a "categories" table without PK'ing the category's ID. I just found out that now the table has a duplicate of all the rows and is causing issues with some of my queries.

My question is: Is there a way to delete duplicate rows, leaving the 'originals' in the table, without relying on indexes?

Here's the

SELECT * FROM categories
:

+-----------+-------------+
| categoria | descripcion |
+-----------+-------------+
| 1 | Int.Cons. |
| 2 | Delegado |
| 3 | Personal |
| 4 | Comun |
| 5 | Proveedor |
| 6 | Menor/Inc |
| 11 | N/Categoria |
| 1 | Int.Cons. |
| 2 | Delegado |
| 3 | Personal |
| 4 | Comun |
| 5 | Proveedor |
| 6 | Menor/Inc |
| 11 | N/Categoria |
+-----------+-------------+


There's no index defined on the table.

Answer

You can delete the records in a couple of ways. I would probably recommend using a temporary table and then re-populating the table correctly:

create table temp_categories as 
    select c.id, c.descripcion
    from categories c
    group by c.id;

truncate table categories;

insert into categories(id, descripcion)
    select id, descripcion
    from temp_categories;


alter table add constraint primary key (id);
alter table add constraint unq_categories_descripcion unique (descripcion);
Comments