CODEWITHSUNDEEP
mysql
Arnie
Arnie

Reputation: 681

Reset all values for a column except for one

This is my query which returns me the count of the gtin from table gtin in table artikel.

 SELECT   g.gtin,
     Count(a.gtin)
 FROM     artikel a
 JOIN     gtin g
     where    a.gtin IN (g.gtin)
 GROUP BY a.gtin
 HAVING   count(a.gtin) > 1

Now I want to reset all gtins in table artikel except for one. Which one does not matter. In the end each gtin from table gtin should only be used once in table artikel in column gtin.

Upvotes: 0

Views: 38

Answers (1)

Henning Koehler
Henning Koehler

Reputation: 2637

I take it that you want to make sure that you don't have duplicate values of gtin in table artikel, and that this is unrelated to references to table gtin.

If you are using MySQL 8.0, this should be easily doable using a window function. Something like

update artikel set gtin = null
where row_number() over (partition by gtin) > 1

Haven't tested it, so there may be syntax errors.

Edit: If your MySQL version is lower, you can select a row from each group to keep its value by utilizing some distinguishing attribute, like artikel_id:

update artikel as a
join ( select gtin, min(artikel_id) as chosen_id
       from artikel group by gtin ) as b
    on b.gtin = a.gtin and a.artikel_id <> b.chosen_id
set a.gtin = null;

Upvotes: 1

Related Questions