Update Duplicate Rows Column in MySQL with minimum ID

Question

There are hundreds of rows in MySQL, which most of them are duplicates to each other. So here I want to update the duplicate rows column "Update" with minimum row ID.

+----+------+-----+--------+
| ID | Name | Age | Update |
+----+------+-----+--------+
| 1  | John | 16  | NULL   |
| 2  | John | 16  | 1      |
| 3  | John | 16  | 1      |
| 4  | John | 16  | 1      |
+----+------+-----+--------+

Here in the above example, minimum row ID is "1". Duplicate rows with same "Name" and "Age" are updated in "Update" column with "1".

I know to delete the duplicate rows, keeping the minimum row ID.

DELETE FROM `students` 
  WHERE ID NOT IN (
    SELECT * FROM (
      SELECT MIN(ID) FROM students 
        GROUP BY name, age
    ) 
  x);

But now I want to update the duplicate rows.

Answer 1

You can update all rows to contain the minimum ID of all duplicate rows and use a second update to set the update column to NULL if it is the same as ID column.

UPDATE Table1 AS u 
SET `Update` = (
    SELECT MIN(ID) 
    FROM (
        SELECT * FROM Table1 AS t
    ) AS l 
    GROUP BY l.Name, l.Age 
    HAVING l.Name = u.Name AND l.Age = u.Age
);
UPDATE Table1 SET `Update` = NULL WHERE `Update` = ID;

SQL Fiddle