Update field based on value of another field in the same table

Question

This is a big problem for me and it seems like it is too simple to be discussing this, however I may be over-complicating it. It's in SQL.

Here is my situation, I have a table called Monthly_Hierarchy as follows:

SITENUM       LOCATION_ID         FRANCHISE
1101          5522005             R
1101          5522005             F
1102          5522005             R
1102          5522005             F

1103          5522006             R
1103          5522006             F
1104          5522006             R
1104          5522006             F

There are about 3204 entries in this table like this. What I want to simply do is make all the sitenums for the same location_id which have the same franchise_type, the same.

So, after modification, my table should ideally look like this:

SITENUM       LOCATION_ID         FRANCHISE
1101          5522005             R
1102          5522005             F
1101          5522005             R
1102          5522005             F

1103          5522006             R
1104          5522006             F
1103          5522006             R
1104          5522006             F

So for ex., all sitenums for 5522005 should be 1101 if the franchise is 'F' and 1102 if the franchise is 1102. I have 2 separate tables with sitenums and locations for all F franchises and the same for all R franchises. Currently my code looks like this:

BEGIN TRANSACTION 
    DECLARE @NewSiteNum int 
    SELECT @NewSiteNum = MAX(SiteNum)+1 FROM [Monthly Hierarchy Table] 

    UPDATE Monthly_Hierarchy  
    SET SITENUM = SELECT @NewSiteNum, 
        @NewSiteNum = @NewSiteNum + 1
    FROM Monthly_Hierarchy m
      JOIN F_hierarchy f
        on m.location_id = f.location_id
    where m.franchise='f'
END

    UPDATE Monthly_Hierarchy  
    SET SITENUM = R.sitenum
    FROM Monthly_Hierarchy m
      JOIN R_hierarchy r
        on m.location_id = r.location_id
    where m.franchise='R'

I just can't figure out how to get from my original table to the final table with my current code. What i keep getting is incremented sitenums for F franchises for the same location id as follows:

SITENUM       LOCATION_ID         FRANCHISE
1101          5522005             R
1106          5522005             F
1101          5522005             R
1107          5522005             F

1103          5522006             R
1108          5522006             F
1103          5522006             R
1109          5522006             F

How do i fix this and get them to be all uniform? Any help would be greatly appreciated.

Answer 1

Judging by the code, you're incrementing @NewSiteNum twice. Once up top in the SELECT @NewSiteNum = MAX(SiteNum)+1 and again in SET SITENUM = SELECT @NewSiteNum, @NewSiteNum = @NewsiteNum + 1

That's where the 1103, 1108, 1109 are coming from. You have 1101 incrementing twice to 1103, 1106 incrementing twice to 1108, etc.

Can you explain why you're incrementing them? I'm not sure if I'm misreading your question but it seems to me like you just want to do a sort of lookup, compare and replace instead of incrementing anything.