I have a table with Id, Site_Name, MEMSUB and Indictaor columns… now my requirement is to update the Indicator column based upon the Id Column

Below is my table,

Id  Site_Name   MEMSUB  Indicator 
1   OEB 503280476   1
2   OEB 801730793   1
3   OEB 805152312   1
4   OEB 815049353   1
5   OEB 830205279   1
6   OEB 837599038   2
7   OEB 841547208   2
8   OEB 843383115   2
9   OEB 851886512   2
10  OEB 862178473   2
11  OEB 865615389   3
12  OEB 865846423   3
13  OEB 871162028   3
14  OEB 876136602   3
15  OEB 876183644   3
16  OEB 989898123   4

I have tried with the below Update with CASE Statement,

UPDATE Fin
SET Fin.Indictaor = (CASE WHEN Fin.Id BETWEEN 1 AND 5 THEN 1
WHEN Fin.Id BETWEEN 6 AND 10 THEN 2
WHEN Fin.Id BETWEEN 11 AND 15 THEN 3….. END) 
FROM dbo.Tbl_Dist_MEMSUB_Dtl AS Fin

Here the issue is, In dbo.Tbl_Dist_MEMSUB table there are around 10 to 15 thousand records are available. I can’t write the CASE statement for 10 to 15 thousand records…!! Can anyone provide me the SQL solution to handle this in a dynamic way….

CodePudding user response：

Expanding on the comment by @Larnu, consider specifying the calculation (id - 1) / 5 1 as a computed column so you don't need to update rows:

CREATE TABLE dbo.YourTable(
     Id  int NOT NULL
    ,Site_Name char(3) NOT NULL
    ,MEMSUB  int NOT NULL
    ,Indictator AS ((id - 1) / 5   1)
);
INSERT INTO dbo.YourTable VALUES
     (1   ,'OEB', 503280476)
    ,(2   ,'OEB', 801730793)
    ,(3   ,'OEB', 805152312)
    ,(4   ,'OEB', 815049353)
    ,(5   ,'OEB', 830205279)
    ,(6   ,'OEB', 837599038)
    ,(7   ,'OEB', 841547208)
    ,(8   ,'OEB', 843383115)
    ,(9   ,'OEB', 851886512)
    ,(10  ,'OEB', 862178473)
    ,(11  ,'OEB', 865615389)
    ,(12  ,'OEB', 865846423)
    ,(13  ,'OEB', 871162028)
    ,(14  ,'OEB', 876136602)
    ,(15  ,'OEB', 876183644)
    ,(16  ,'OEB', 989898123);

SELECT
     Id
    ,Site_Name
    ,MEMSUB
    ,Indictator
FROM dbo.YourTable;