Update only the first record from duplicate records in MySQL

Question

SOLVED problem!

Update:

Not quite right what I need, let's give an example on a simple table with field identifiers, NAME, COVER

I have 100 entries with 100 names, some of them are duplicated, but I only want to update them from duplicates first.

---

Trying to update all 1st row of all duplicates in the database, it is really difficult to do this, any idea how I can do this? Below is the code I'm trying to rebuild, but this code replaces every first with last for all duplicates.

Outline how i want it to work below

ID NAME COVER 1 Max 1 2 Max 0 3 Andy 1 4 Andy 0 5 Andy 0 UPDATE table t JOIN ( SELECT MinID, b.Name LatestName FROM table b JOIN ( SELECT MIN(ID) MinID, MAX(ID) MaxID FROM table GROUP BY tag HAVING COUNT(*) > 1 ) g ON b.ID = g.MaxID ) rs ON t.ID = rs.MinID SET t.Name = LatestName; 

Answer 1

It is not clear what you want. Perhaps it:

 UPDATE table AS t JOIN ( SELECT MIN(ID) MinID FROM table GROUP BY Name HAVING COUNT(*) > 1 ) AS m ON t.ID = m.MinID SET t.Cover = 1 ; 

---

For this (and future) question, keep in mind when you write a question:

 1. a description of your problem, as clear as possible --- you have that 2. data you have now (a few rows of the tables) --- ok, nice 3. the code you have tried --- yeah, but better use same names --- as the data and description above 4. the error you get (if you get an error) --- doesn't apply here 5. the result you want (the rows after the update in your case) --- so we know what you mean in case we --- haven't understood from all the rest 

Answer 2

I did this recently in PostgreSQL. Can you use a temporary table? If so, for each set of duplicates, insert the primary key MIN () in your temporary table, and then do UPDATE using the where clause using PK in the temp table.

Edit: after your comment, here is what I did recently.

 CREATE TEMPORARY TABLE misc_updates ( temp_oid INTEGER, job INTEGER, run CHARACTER VARYING(8), quantity INTEGER ); INSERT INTO misc_updates (temp_oid, job, run, quantity) SELECT MAX(runjob.oid) temp_oid, runjob.job, runjob.run, SUM(runjob.quantity) sum_quantity FROM runjob INNER JOIN job ON (runjob.job = job.code) INNER JOIN (SELECT run, job FROM runjob GROUP BY run, job HAVING COUNT(*) > 1) my_inner ON (runjob.run = my_inner.run AND runjob.job = my_inner.job) GROUP BY runjob.job, runjob.run, job.quantity ; /* Do updates on one of the duplicated runjob rows */ UPDATE runjob SET quantity = mu.quantity FROM misc_updates mu WHERE runjob.oid = mu.temp_oid; 

You can change the "oid" for your primary key (my problem was that the table did not have a primary key!). The where clause in UPDATE is also critical, so only some rows are updated. You will need to change MAX () to MIN () and, of course, change the lines to those used in your use case. Keep in mind that this is for PostgreSQL, but the approach should be the same.

Answer 3

Use subquery as selection criteria:

 UPDATE table t SET t.Name = LatestName WHERE ID = (SELECT ID FROM table WHERE ( SELECT COUNT(DISTINCT(Name)) FROM table WHERE Name = 'duplicate' ) > 1 LIMIT 1)