Update only first record from duplicate entries in MySQL

Question

Problem SOLVED!

Update:

Not quite right what I need, lets do example on simple table with fields ID,NAME,COVER

I have got 100 entries with 100 names, some of the names are duplicated, but I want only update first one from duplicates.

Trying to update all the 1st rows from all the duplicates in database, really hard to do it, any idea how I can make it? Below is the code I am trying to rebuild, but this code replace every 1st one with the last one for all the duplicates.

Schema, how I want it 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;

I think u write very confusing query, would u please tell us table schema... — xkeshav, Oct 01 '11 at 11:54
Or do you want to update **all rows except the last one** with the last one's Name? — ypercubeᵀᴹ, Oct 01 '11 at 12:03
update every first row with 1 and schema is very simple ID,Name,Cover — user974435, Oct 01 '11 at 12:04
I want to update every 1st Cover = 1 as the Name is duplicated — user974435, Oct 01 '11 at 12:05
Edit your question and add what you want the result to be. It's not clear at all. — ypercubeᵀᴹ, Oct 01 '11 at 12:15

ypercubeᵀᴹ · Accepted Answer · 2011-10-01T12:32:19.480

It's not clear at all what you want. Perhaps this:

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

You might be interested in this post: http://stackoverflow.com/questions/28802347/mysql-cleanup-table-from-duplicated-entries-and-relink-fk-in-depending-table/28802348#28802348 — Cedric Simon, Mar 02 '15 at 03:39
@CedricSimon Nice. Yes, foreign keys tend to complicate things ;) — ypercubeᵀᴹ, Mar 02 '15 at 11:53

score 1 · Answer 2 · answered Oct 01 '11 at 12:10

1

Use a subquery as selection criterium:

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)

answered Oct 01 '11 at 12:10

Friek

1,533
11
13

halfer · Answer 3 · 2011-10-01T12:33:00.320

I did this recently in PostgreSQL. Are you in a position to use a temporary table? If so, for each duplicate set, insert the MIN() primary key into your temp table, and then do your UPDATE using a where clause using the PKs in the temp table.

Edit: following your comment, here is something 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 swap 'oid' for your primary key (my problem was that the table had no primary key!). Also the critical thing is the where clause in the UPDATE, so only some rows are updated. You'll need to swap out MAX() for MIN(), and of course change the rows to the ones in your use-case. Bear in mind that this is for PostgreSQL, but the approach should be much the same.

Update only first record from duplicate entries in MySQL

3 Answers3

Linked