I need to check a table and determine if there is not a value greater than 0 where the academic period Id's are the same. If there is no value greater than 0, I want to update the isActive column in the common set of academic period Ids where the Progress Step Id is 1
For Example:
In the table below, I have 8 steps for each academic period. I need to check where there are no active steps for an academic period (as is the case for academic period 1) and set the isActive to 1 for the ProgressStepId of 1 as seen in the example for the Academic Period 2
| Id | isActive | ProgressStepId | AcademicPeriodId |
|---|---|---|---|
| 1 | 0 | 1 | 1 |
| 2 | 0 | 2 | 1 |
| 3 | 0 | 3 | 1 |
| 4 | 0 | 4 | 1 |
| 5 | 0 | 5 | 1 |
| 6 | 0 | 6 | 1 |
| 7 | 0 | 7 | 1 |
| 8 | 0 | 8 | 1 |
| 9 | 1 | 1 | 2 |
| 10 | 0 | 2 | 2 |
| 11 | 0 | 3 | 2 |
| 12 | 0 | 4 | 2 |
| 13 | 0 | 5 | 2 |
| 14 | 0 | 6 | 2 |
| 15 | 0 | 7 | 2 |
| 16 | 0 | 8 | 2 |
I have the following script where the sub select queries work, but when combined with the update statement, I receive the 1093 You can't specify target table 'ProgressStepsAcademicPeriod' for update in FROM clause
UPDATE ProgressStepsAcademicPeriod SET isActive = 1 WHERE
ProgressStepId = 1 IN
(SELECT DISTINCT ProgressStepId
FROM ProgressStepsAcademicPeriod
WHERE AcademicPeriodId IN
(SELECT AcademicPeriodId
FROM ProgressStepsAcademicPeriod
GROUP BY AcademicPeriodId
HAVING (count(case when isActive = 1 then 1 end) = 0))
ORDER BY ProgressStepId)
