Eray Balkanli Eray Balkanli - 16 days ago 5
SQL Question

How to update a field's value based on a match condition for current data and one another?

I have a table

#temp
in sql server 2008 like:

Id p_id h_id ProvincialPID
1 100 A1256 null
2 200 A7865 null
3 300 A5555 null
4 100 A1256 null
5 300 A5555 null
6 400 A7865 null
...


Please note that for the records
p_id
values are equal,
h_id
values must be equal (check 100 and 300)! On the other hand, there are some record whose
p_id
values are not equal but
h_id
values are equal (like 200 and 400)!

What I want is to update
ProvincialPID
column based on the fact that: if a record's
p_id
is matching another record's
p_id
, then
ProvincialPID = p_id
, else if a record's
p_id
does not match any others but its
h_id
matches at least one other, then
ProvincialPID = h_id


Example: For the sample data above, I am supposed to see:

Id p_id h_id ProvincialPID
1 100 A1256 100
2 200 A7865 A7865
3 300 A5555 300
4 100 A1256 100
5 300 A5555 300
6 400 A7865 A7865
...


The code below is helping me to perform it. However, it is extremely slow and I would like to apply a more efficient code. But I could not find a way to make it more efficient. Any help/advice will be so appreciated.

DECLARE @counter INT = 0;
DECLARE @currentPID varchar(50);
DECLARE @currentID varchar(50);
DECLARE @currentHID varchar(50);

WHILE @counter < (select COUNT(*) from #temp)
BEGIN
set @currentID = (select top (1) x.Id from (select top(@counter) * from #temp order by Id desc) x)
set @currentPID = (select top (1) x.p_id from (select top(@counter) * from #temp order by Id desc) x)
set @currentHID = (select top (1) x.h_id from (select top(@counter) * from #temp order by Id desc) x)

if((select COUNT(*) from #temp t where t.Id != @currentID and t.p_id = @currentPID) > 0)
update #temp
set ProvincialPID = @currentPID
where Id = @currentID
else if ((select COUNT(*) from #temp t where t.Id != @currentID and t.p_id != @currentPID and t.h_id = @currentHID) > 0)
update #temp
set ProvincialPID = @currentHID
where Id = @currentID

SET @counter = @counter + 1;
END;

Answer

Okay first some general advice - if you ever find yourself doing while loops, or using cursors in SQL, you are probably doing it wrong. You almost never need them. Instead think about writing queries to get the set of data you want, and apply the change to the whole set in one go.

Here is what I think you need... works for you example data at least.

create table #temp
(
    Id int,
    p_id int,
    h_id varchar(5),
    ProvincialPID varchar(5)
);

insert into #temp(Id, p_id, h_id) values(1,100,'A1256');
insert into #temp(Id, p_id, h_id) values(2,200,'A7865');
insert into #temp(Id, p_id, h_id) values(3,300,'A5555');
insert into #temp(Id, p_id, h_id) values(4,100,'A1256');
insert into #temp(Id, p_id, h_id) values(5,300,'A5555');
insert into #temp(Id, p_id, h_id) values(6,400,'A7865');

update #temp
set ProvincialPID = p_id
where p_id in (select p_id from #temp group by p_id having count(*) > 1)

update #temp
set ProvincialPID = h_id
where p_id not in (select p_id from #temp group by p_id having count(*) > 1)
and h_id in (select h_id from #temp group by h_id having count(*) > 1)

select * from #temp