Change duplicate value in a column

Question

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.

@jarlh, I need to replace the found duplicate with the line -. but only with this condition, for example, four models are found in the Model column: Esonic G31CEL2 Esonic G31CEL2 Esonic G31CEL2 Esonic G31CEL2 at the output you need to get: Esonic G31CEL2 - - - — Alex Rebell
– Alex Rebell, Commented Jan 27, 2021 at 9:16
Post sample data and expected results to clarify what you want. — forpas
– forpas, Commented Jan 27, 2021 at 10:10

Barbaros Özhan · Accepted Answer · 2021-01-27 12:09:03Z

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

answered Jan 27, 2021 at 12:09

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Popeye · Accepted Answer · 2021-01-27 12:11:46Z

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)

edited Jan 27, 2021 at 12:11

answered Jan 27, 2021 at 9:29

Popeye

36k4 gold badges12 silver badges31 bronze badges

7 Comments

Srijon Chakraborty Over a year ago

Your query contains error. Please check it . :)

Popeye Over a year ago

I am not able to see any error. Please point out if you find any @Linker.

Srijon Chakraborty Over a year ago

Alias name.update Devices d creating an error(SQL SERVER 2016). Please check . :)

Popeye Over a year ago

@Linker Yes, changed it now. Thanks for pointing it out. :)

Alex Rebell Over a year ago

@Popeye, I am trying to use your request, but I have an error: Execution finished with errors. Result: near ".": syntax error At line 1: update d set d.

|

Alex Rebell · Accepted Answer · 2021-01-27 12:38:44Z

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.

edited Jan 27, 2021 at 12:38

answered Jan 27, 2021 at 9:25

Alex Rebell

3744 silver badges20 bronze badges

2 Comments

Srijon Chakraborty Over a year ago

It will create another duplicate contained row if the any group does have more than 2 items.

Alex Rebell Over a year ago

@Linker, it's strange why you think so, for a start I looked, what are the duplicates, there are many of them. I'll update my answer.

Collectives™ on Stack Overflow

Change duplicate value in a column

3 Answers 3

Comments

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related