CODEWITHSUNDEEP
phpmysql
user974435
user974435

Reputation: 397

Update only first record from duplicate entries in MySQL

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;

Upvotes: 5

Views: 8567

Answers (3)

halfer
halfer

Reputation: 20469

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.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

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

Upvotes: 8

Friek
Friek

Reputation: 1541

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)

Upvotes: 1

Related Questions