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 | Possession |
|---|---|---|---|
| 99 | 1 | 1 | 0 |
| 99 | 1 | 2 | 0 |
| 99 | 2 | 1 | 0 |
| 99 | 2 | 2 | 0 |
| 99 | 3 | 1 | 0 |
| 99 | 3 | 2 | 0 |
| 99 | 4 | 1 | 0 |
| 99 | 4 | 2 | 0 |
| 99 | 5 | 1 | 0 |
| 99 | 5 | 2 | 0 |
| 99 | 6 | 1 | 0 |
| 99 | 6 | 2 | 0 |
| 99 | 7 | 1 | 0 |
| 99 | 7 | 2 | 0 |
| 99 | 8 | 1 | 0 |
| 99 | 8 | 2 | 0 |
| 99 | 9 | 1 | 0 |
| 99 | 9 | 2 | 0 |
| 99 | 10 | 1 | 0 |
| 99 | 10 | 2 | 0 |
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 |
CodePudding user response：
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;