0

Can you please tell me what SQL query can I use to change duplicates in one column of my table? I found these duplicates:

SELECT Model, count(*) FROM Devices GROUP BY model HAVING count(*) > 1;

I was looking for information on exactly how to change one of the duplicate values, but unfortunately I did not find a specific option for myself, and all the more information is all in abundance filled by deleting the duplicate value line, which I don't need. Not strong in SQL at all. I ask for help. Thank you so much.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Alex Rebell
  • 465
  • 3
  • 15
  • 2
    Change what value, to what? – jarlh Jan 27 '21 at 09:11
  • 1
    @jarlh, I need to replace the found duplicate with the line `-`. but only with this condition, for example, four models are found in the `Mode`l column: `Esonic G31CEL2` `Esonic G31CEL2` `Esonic G31CEL2` `Esonic G31CEL2` at the output you need to get: `Esonic G31CEL2` `-` `-` `-` – Alex Rebell Jan 27 '21 at 09:16
  • 1
    Post sample data and expected results to clarify what you want. – forpas Jan 27 '21 at 10:10

3 Answers3

1

You can easily use a Window Functions such as ROW_NUMBER() with partitioning option in order to group by Model column to eliminate the duplicates, and then pick the first rows(rn=1) returning from the subquery such as

WITH d AS
(
 SELECT *, ROW_NUMBER() OVER (PARTITION BY Model) AS rn
   FROM Devices 
)
SELECT ID, Model -- , and the other columns
  FROM d
 WHERE rn = 1

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

After the command:

SELECT Model, count (*) FROM Devices GROUP BY model HAVING count (*)> 1;

i get the result:

  • 1895 lines = NULL;
  • 3383 lines with duplicate values;
  • and all these values are 1243.

after applying your command:

update Devices set
  Model = '-'
where id not in
  (select
     min(Devices .id)
   from Devices 
   group by Devices.Model)

i got 4035 lines changed. if you count, it turns out, (3383 + 1895) = 5278 - 1243 = 4035 and it seems like everything fits together, the result suits, it works.

Alex Rebell
  • 465
  • 3
  • 15
0

use exists as follows:

update d 
   set Model = '-'
  from Devices d
where exists (select 1 from device dd where dd.model = d.model and dd.id > d.id)
Popeye
  • 35,427
  • 4
  • 10
  • 31