CODEWITHSUNDEEP
mysqlsql
Sritam
Sritam

Reputation: 3192

Reduce not null duplicate rows in MySQL from many to one

I need an update statement to resolve some issues with duplicates in a table on MySQL. The table structure is shown below. I need a MySQL statement that will set the value of duplicate as NULL except the first one, i.e the one with the lowest id. Here the id is the primary key.

This is an example of what I have:

 id       name
  1        foo
  2        foo
  3        bar
  4        NULL
  5        NULL
  6        foo
  7        bar

This is the desired result:

 id        name 
  1        foo
  2        NULL
  3        bar
  4        NULL
  5        NULL
  6        NULL
  7        NULL

The table has other columns with useful information. Hence, the row can't just simply be deleted.

Upvotes: 0

Views: 148

Answers (3)

Sritam
Sritam

Reputation: 3192

UPDATE t JOIN
   (SELECT name, MIN(id) as min_id
    FROM t
    WHERE name IS NOT NULL
    GROUP BY name
    HAVING COUNT(*) > 1
   ) tt
   ON t.name = tt.name and t.id > tt.min_id
SET t.name = NULL;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I would write this as:

UPDATE t JOIN
       (SELECT name, MIN(id) as min_id
        FROM t
        GROUP BY name
       ) tt
       ON t.name = tt.name and t.id > tt.min_id
    SET t.name = NULL;

I think the logic is easier to follow using JOIN. Basically, it says to find the minimum id for each name. Then set all other rows for the same name to NULL.

Upvotes: 1

hsan
hsan

Reputation: 1559

Your UPDATE query can include a JOIN.

e.g. join the minimum id for each name and only update where the id is not the minimum.

UPDATE
    t
    LEFT OUTER JOIN (
        SELECT
            MIN(id) AS id
        FROM
            t
        GROUP BY
            name
    ) t1 ON t.id = t1.id
SET
    t.name = NULL
WHERE
    t1.id IS NULL

DB Fiddle

Upvotes: 0

Related Questions