Find duplicate records, change them and return them only in MySQL

Question

Find duplicate records, change them and return them only in MySQL

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I have a problem, I have data from a table that is duplicated, inside are the following fields id, empresa, actividad, compañia I need the field empresa is unique, I generate a query to know how many and which are duplicates and I need to delete. I did the query and it throws me the following results (I did it this way because I did not know how to put a table or better organized I hope to make myself understood) :

Empresa | Compañia  | Total
Empr1   | Compañia2 | 2
Empr2   | Compañia3 | 4
Empr3   | Compañia1 | 1

Now I need the company to have only one record per company How do I achieve it? or maybe that the company has only one record without importing the company How do I achieve it?

This is to be able to return in field empresa single.

mysql sql php phpmyadmin

asked by Fernando 28.09.2018 в 16:59

source

2 answers

Bring related data from a table Class shows values that do not correspond to the select

score 2 · Answer 1

Hi, I invite you to analyze the following example, it is illustrative so you can solve your problem:

CREATE TABLE Test (
  Empresa varchar(10),
  Compania varchar(10)
)

INSERT INTO Test VALUES ('Empr1', 'Compania2')
INSERT INTO Test VALUES ('Empr1', 'Compania2')
INSERT INTO Test VALUES ('Empr2', 'Compania3')
INSERT INTO Test VALUES ('Empr2', 'Compania3')
INSERT INTO Test VALUES ('Empr2', 'Compania3')
INSERT INTO Test VALUES ('Empr2', 'Compania3')
INSERT INTO Test VALUES ('Empr3', 'Compania1')

DELETE TablaDerivada
  FROM (SELECT
    Empresa,
    Compania,
    Repetidos = ROW_NUMBER() OVER (PARTITION BY Empresa ORDER BY Compania)
  FROM Test) AS TablaDerivada
WHERE Repetidos > 1

SELECT * FROM Test

The magic is within the FROM that is in the DELETE sequence, which performs a partition by company and orders by company, as each company assigns a number and all are the same at the end (this partition represents the field "Repeated") where you specify that you delete those records where the repeated field is greater than one.

score 0 · Answer 2

I will be guided by the answer of @BytesMan but I will do it differently with Common Table Expressions :

WITH Test_CTE AS
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (PARTITION BY Empresa ORDER BY Compania) AS Duplicado FROM Test
)
DELETE FROM Test_CTE WHERE Duplicado > 1;
SELECT * FROM Test;