CODEWITHSUNDEEP
sqlsql-servert-sql
Ali Khani
Ali Khani

Reputation: 73

Change value of duplicated rows

There is a table with tow columns(ID, Data) and there are 3 rows with same value.

ID          Data
4           192.168.0.22 
4           192.168.0.22 
4           192.168.0.22

Now I want to change third row DATA column. In update SQL Server Generate an error that I ca not change the value.

I can delete all 3 rows. But I can not delete third row separately.

This table is for a software that I bought and I changed the third Server IP.

Upvotes: 1

Views: 125

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try the following query

create table #tblSimilarValues(id int, ipaddress varchar(20))
insert into #tblSimilarValues values (4, '192.168.0.22'),
(4, '192.168.0.22'),(4, '192.168.0.22')

Use Below query if you want to change all rows

with oldData as (
      select *,
             count(*) over (partition by id, ipaddress) as cnt
      from #tblSimilarValues
     )
update oldData
    set ipaddress = '192.168.0.22_1'
    where cnt > 1;
select * from #tblSimilarValues

Use Below query if you want to skip firs row

;with oldData as (
      select *,
             ROW_NUMBER () over (partition by id, ipaddress order by id, ipaddress) as cnt
      from #tblSimilarValues
     )
update oldData
    set ipaddress = '192.168.0.22_2'
    where cnt > 1;

select * from #tblSimilarValues
drop table #tblSimilarValues

You can find the live demo live demo here

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

Since there is no column that allows us to distinguish these rows from each other, there's no "third row" (nor a first or second one for that matter).

We can use a ROW_NUMBER function to apply arbitrary row numbers to these rows, however, and if we place that in a CTE, we can apply DELETE/UPDATE actions via the CTE and use the arbitrary row numbers:

declare @t table (ID int not null, Data varchar(15))
insert into @t(ID,Data) values
(4,'192.168.0.22'),
(4,'192.168.0.22'),
(4,'192.168.0.22')

;With ArbitraryAssignments as (
    select *,ROW_NUMBER() OVER (PARTITION BY ID, Data ORDER BY Data) as rn
    from @t
)
delete from ArbitraryAssignments where rn > 2

select * from @t

This produces two rows of output - one row was deleted.

Note that I say that the ROW_NUMBER is arbitrary. One of the expressions in both the PARTITION BY and ORDER BY clauses is the same. By definition, then, we know that no real ORDER is defined by this (because all rows within the same partition, by definition, have the same value for that expression).

Upvotes: 2

TheSprinter
TheSprinter

Reputation: 1528

In this case ID columns allows duplicate value which is wrong, ID should be unique.
Now what you can do is create a new column make that unique or Primary Key or change the duplicate values of ID column and make it Unique/Primary key.
Now as per your Unique key/Primary key you can update DATA column value by query as below: 

UPDATE <Table Name>
SET DATA = 'new data'
WHERE ID = 3;

Upvotes: 1

Related Questions