Eray Balkanli Eray Balkanli - 14 days ago 5
SQL Question

How to update a field's value by an incremental value without using loop in SQL Server 2008?

I have a table

#temp
in SQL Server 2008 like this:

Id p_id h_no f_id
------------------
1 100 A01 null
2 200 A02 null
3 300 A02 null
4 400 null null
5 500 null null
6 600 A03 null
7 700 A01 null
8 400 null null


So basically, every record has a
p_id
, but may or may not have
h_no
.

What I want is to replace
f_id
values with a dummy incremental number based on:


  • if
    h_no
    value of a record matches another(s), this (those) ones will have same
    f_id
    (check ids:1 & 7 or ids:2 & 3 in the example)

  • if
    h_no
    is null but
    p_id
    values are equal for some cases, they will have same
    f_id
    (check ids: 4 & 8 in the example)



For example, the sample table above should be:

Id p_id h_no f_id
-----------------
1 100 A01 1
2 200 A02 2
3 300 A02 2
4 400 null 3
5 500 null 4
6 600 A03 5
7 700 A01 1
8 400 null 3


I do not want to use a loop for this process. I am trying to find a more optimal solution for this. I need a query something like below, could not find the correct syntax.

declare @tempFID int = 1;

update t
set t.f_id = @tempFID++ --syntax error
from #temp t
inner join #temp t2 on t.Id = t2.Id
where (t.h_no is not null and t.h_no = t2.h_no)
or (t.h_no is null and t.p_id = t2.p_id)


I also tried but had syntax error:

update t
set t.f_id = (set @tempFID = @tempFID + 1) --syntax error
...


Any help would be so appreciated!

Answer
;WITH cte AS (
    SELECT *
       ,CASE WHEN h_no IS NULL THEN p_id ELSE MIN(p_id) OVER (PARTITION BY h_no) END as PIdGroup
    FROM
       @Table
)

, cteFIdValue AS (
    SELECT
       Id
       ,DENSE_RANK() OVER (ORDER BY PIdGroup) as f_id
    FROM
       cte
)

UPDATE t
    SET f_id = u.f_id
FROM
    Table t
    INNER JOIN cteFIdValue u
    ON t.ID = u.ID
  • Find the minimum p_id for each h_no and just leave it as the assigned p_id if h_no is null
  • Then create a dense rank on the PidGroup
  • Update the Table

so you have problems besides a syntax error in your code above. First your join will only get the exact same record, you would have to change to t.ID <> t2.ID as left join and still need some sort of ranking. honestly I am not positive what you are attempting there.