Eray Balkanli Eray Balkanli - 1 year ago 61
SQL Question

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

I have a table

in sql server 2008 like

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
, but may or may not have

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

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

  • if
    is null but
    values are equal for
    some cases, they will have same
    (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 Source
;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

, cteFIdValue AS (
       ,DENSE_RANK() OVER (ORDER BY PIdGroup) as f_id

    SET f_id = u.f_id
    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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download