CODEWITHSUNDEEP
sqlsql-server
eVolve
eVolve

Reputation: 1446

How to find duplicate values and then update a field in the one table using sql?

I have the following table:

Table Name: Tp

Columns: TpId | PId | TId

How can I update TId column to the value of 3 for every row where PId is duplicated.

Upvotes: 0

Views: 53

Answers (3)

kobik
kobik

Reputation: 21232

If you need to update all rows where Pid is duplicated you can use something like this:

update Tp set Tid=3 where Pid in
(
  select Pid
  from Tp
  group by Pid
  having COUNT(*) > 1
)

Upvotes: 1

forpas
forpas

Reputation: 164069

With EXISTS:

update  t
set tid = 3
from tp t
where exists (
  select 1 from tp
  where tpid <> t.tpid and pid = t.pid
)

I guess that tpid is unique in your table.
See the demo.
If you want to update only all the duplicated rows except the first, then:

update t
set tid = 3
from tp t
where exists (
  select 1 from tp
  where tpid < t.tpid and pid = t.pid
)

See the demo.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can use row_number()

with cte as
(
select *,row_number()over(partition by PId order by TpId ) rn
from Tp
) update cte
  set  TId=3
  where rn=1

Upvotes: 1

Related Questions