setting multiple values equal to one value based on PK

[gixer]

Given the table below:

iprofileid iownerid(pk) isurveyid
10 9 10
12 9 35
55 1 10
56 1 35

how would one write an update statement so that the iprofileid's are the same for the primary key iownerid. E.g. iprofileid would be 10 and 10 for iownerid of 9, and iprofileid would be 55 and 55 for iownerid of 1. Sure this could be done manually one at a time, but I am talking about thousands of records.

 

[VogonPoet]

I didn't test this, but it should work. I would suggest testing it on a copy of the table to make sure it is giving you the results you expect.

Update t
set t.iprofileid=s.iprofileid
from table t
inner join
(
Select iownerid, min(iprofileid) as iprofileid
from table
group by iownerid
) s on t.iownerid=s.iownerid

Michael

 

[bperry]

It sounds like:
for each row, set the iprofileid to the lowest iprofileid available for that iownerid. Is that right?

update itable set iprofileid =
(select MIN(iprofileid) from iTable t2
where t2.iownerid = iTable.iownerid)

By the way, Primary Key's are required to be unique, so in your example data iownerid cannot be the PK.