A MySQL question is a table of the form

id int(10) PRIMARY KEY name VarChar(50)

If the name field coincides, delete its value! all but the first.

those. select records whose name occurs more than 1 time, and for all but the smallest id , reset the name field .. are there ways? for 1 request ....?

    2 answers 2

    In MySQL, unfortunately, this task cannot be solved in one request. However, there is a method using an intermediate table. You can do, for example, like this:

     CREATE TEMPORARY TABLE temp AS SELECT MIN(id) id FROM table GROUP BY name; UPDATE table SET name = null WHERE id NOT IN (SELECT id FROM temp);

    Or using your favorite programming language to fulfill the request

      SELECT MIN(id) id FROM table GROUP BY name;

    And substitute the result of its execution in UPDATE.

    UPD> A small explanation of the request - it selects the ID of records with, so to speak, unique NAME. Therefore, in the future, the query is not updated and NOT IN is used. We are updating records that are not included in this list.

    • I do not quite understand the second request, if not difficult to explain in Russian what it will choose for me (not aware of what is HAVING, and did not understand this moment MIN (id) id, is there really no comma here?). Now implemented by several requests + processing in "my favorite language." 1) there is a selection of all duplicates, then in your favorite language, the minimum ID for each unique name is deleted, and the result is substituted into the Update as an array. - Vladimir Klykov
    • @_rMx and doesn’t it roll? (I'm really not sure, but check for laziness) UPDATE table SET name = null WHERE id NOT IN (SELECT MIN (id) id FROM table GROUP BY name HAVING COUNT (*)> 1); In theory, a nested select just creates a temporary table - zb '
    • @eicto I checked, my MySQL 5.5 swore that you can't use an updatable table in WHERE. @ ToRcH565 MIN (id) id is equivalent to MIN (id) AS id. With HAVING deceived, updated the answer. Wrote at night, the brain rustled with difficulty)) - _rMX_
    • Having fooled MySQL or you? :) Check, only the necessary data has been updated? - draev
    • I got to potestit) your options give the opposite result) I do not need to know the minimum ID, I need to find out all IDs except the minimum! and reset the name field there. - Vladimir Klykov

    You can completely remove such entries using a query.

     DELETE d FROM `tbl` AS d JOIN (SELECT name, MIN(id) AS minid FROM `tbl` GROUP BY name HAVING COUNT(*) > 1) AS dbl ON d.name = dbl.name AND d.id > dbl.minid

    If you only need to null the name value for duplicates, you can use the following UPDATE query

     UPDATE tbl JOIN (SELECT name, MIN(id) AS minid FROM tbl GROUP BY name HAVING COUNT(*) > 1) AS dbl ON tbl.name = dbl.name AND tbl.id > dbl.minid SET tbl.name = ''