Home T-SQL update column with case statement when between two values from another table in SQL Server

Questions

T-SQL update column with case statement when between two values from another table in SQL Server

January 3, 2022

I have two tables and I would like to update the Possession column in Table1 when day is between StartDay and EndDay of Table2.

Table1

CompanyId	Day	GroupId
99	1	1
99	1	2
99	2	1
99	2	2
99	3	1
99	3	2
99	4	1
99	4	2
99	5	1
99	5	2
99	6	1
99	6	2
99	7	1
99	7	2
99	8	1
99	8	2
99	9	1
99	9	2
99	10	1
99	10	2

Table2

CompanyId	GroupId	StartDay	EndDay
99	1	1	3
99	2	4	5
99	1	6	7
99	2	8	10

This is the update statement I wrote but Table1 is only updating for the first row of Table2. I need it to update for every row of Table2.

UPDATE Table1
SET Table1.Possession = 
    CASE 
        WHEN a.Day BETWEEN b.StartDay AND b.EndDay
             AND a.GroupId = b.GroupId 
        THEN 1
        ELSE 0
    END
FROM Table1 a
INNER JOIN Table2 b ON a.CompanyId = b.CompanyId

This is my desired outcome

CompanyId	Day	GroupId	Possession
99	1	1	1
99	1	2	0
99	2	1	1
99	2	2	0
99	3	1	1
99	3	2	0
99	4	1	0
99	4	2	1
99	5	1	0
99	5	2	1
99	6	1	1
99	6	2	0
99	7	1	1
99	7	2	0
99	8	1	0
99	8	2	1
99	9	1	0
99	9	2	1
99	10	1	0
99	10	2	1

>Solution :

Your join condition is not correct, each row from a is matching with each row of b so you are updating each row of a 4 times, which 1 of the 4 should be used?

You could use an outer join with all criteria but this is better written using exists

Update a 
 set a.possession=
 case when exists (
    select * from table2 b 
    where b.CompanyId=a.companyId 
      and b.groupId=a.groupId 
      and a.day between b.startday and b.endday
) then 1 else 0 end
from 
table1 a;