Use SQL statements to delete duplicates and keep only one

In thousands of records, there are some same records, how to use SQL statement, delete the duplicate

Please pay attention to the red

Select peopleId from peopleId, peopleId from peopleId, peopleId from peopleId, peopleId from peopleId

SELECT
    *
FROM
    people
WHERE
    peopleId IN (
        SELECT
            peopleId
        FROM
            people
        GROUP BY
            peopleId
        HAVING
            count(peopleId) > 1
    )
Copy the code

Delete unnecessary duplicate records from the table. Duplicate records are judged by a single field (peopleId), and only those with the smallest ROWID remain

DELETE
FROM
    people
WHERE
    peopleName IN (
        SELECT
            peopleName
        FROM
            people
        GROUP BY
            peopleName
        HAVING
            count(peopleName) > 1
    )
AND peopleId NOT IN (
    SELECT
        min(peopleId)
    FROM
        people
    GROUP BY
        peopleName
    HAVING
        count(peopleName) > 1
)
Copy the code
Mysql > alter table mysql > alter table mysql > alter table mysql > alter table mysql

1093 – You can’t specify target table ‘student’ for update in FROM clause When data is updated, a query is used, and the query data is updated with the conditions. Mysql does not support this method. How do you get around this problem? Add another layer of encapsulation as follows:

DELETE
FROM
    people
WHERE
    peopleName IN (
         SELECT
            peopleName
        FROM(
             SELECT
                peopleName
            FROM
                people
            GROUP BY
                peopleName
            HAVING
                count(peopleName) > 1
        ) a
    )
AND peopleId NOT IN (
    SELECT
            peopleName
    FROM(
        SELECT
            min(peopleId)
        FROM
            people
        GROUP BY
            peopleName
        HAVING
            count(peopleName) > 1
    ) b
)
Copy the code

Select * from table where duplicate entries (multiple columns) exist

SELECT
    *
FROM
    vitae a
WHERE
    (a.peopleId, a.seq) IN (
        SELECT
            peopleId,
            seq
        FROM
            vitae
        GROUP BY
            peopleId,
            seq
        HAVING
            count(*) > 1
    )
Copy the code

4. Delete redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest ROWID

DELETE
FROM
    vitae a
WHERE
    (a.peopleId, a.seq) IN (
        SELECT
            peopleId,
            seq
        FROM
            vitae
        GROUP BY
            peopleId,
            seq
        HAVING
            count(*) > 1
    )
AND rowid NOT IN (
    SELECT
        min(rowid)
    FROM
        vitae
    GROUP BY
        peopleId,
        seq
    HAVING
        count(*) > 1
)
Copy the code

5. Find redundant duplicate records (multiple fields) in the table, excluding the smallest roWID record

SELECT
    *
FROM
    vitae a
WHERE
    (a.peopleId, a.seq) IN (
        SELECT
            peopleId,
            seq
        FROM
            vitae
        GROUP BY
            peopleId,
            seq
        HAVING
            count(*) > 1
    )
AND rowid NOT IN (
    SELECT
        min(rowid)
    FROM
        vitae
    GROUP BY
        peopleId,
        seq
    HAVING
        count(*) > 1
)
Copy the code

6. Eliminate the first left of a field:

UPDATE tableName 
    SET [ Title ]= RIGHT ([ Title ],(len([ Title ]) - 1))
WHERE
    Title LIKE 'village %'
Copy the code

7. Eliminate the first digit to the right of a field:

    UPDATE tableName
SET [ Title ]= LEFT ([ Title ],(len([ Title ]) - 1))
WHERE
    Title LIKE '% village'

Copy the code

Delete unnecessary duplicate records (multiple fields) in the table, excluding the record with the smallest ROWID

UPDATE tableName 
SET key = value WHERE key1 IN (
SELECT key1 FROM vitae GROUP BY peopleId)
Copy the code

If the method is similar to the above, ask to see if you need to include the condition again.

Reproduced above:Blog.csdn.net/u014723529/…