I get this error whenever I change the main teacher for a course section. It occurs when I create a new section and try and change the main teacher and also existing courses. Ant ideas?
DB Execute Failed. 1093 You can't specify target table 'ac' for update in FROM clause
Failure Notice
UPDATE attendance_completed ac SET STAFF_ID='60'
WHERE ac.STAFF_ID='59'
AND ac.PERIOD_ID='49'
AND ac.SCHOOL_DATE IN(SELECT acc.SCHOOL_DATE
FROM attendance_calendar acc,school_periods sp,course_periods cp,course_period_school_periods cpsp
WHERE cpsp.PERIOD_ID='49'
AND sp.PERIOD_ID=cpsp.PERIOD_ID
AND cp.COURSE_PERIOD_ID='237'
AND cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID
AND acc.CALENDAR_ID=cp.CALENDAR_ID
AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cpsp.DAYS)>0 OR (sp.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)))
AND ac.SCHOOL_DATE BETWEEN (SELECT START_DATE
FROM school_marking_periods
WHERE MARKING_PERIOD_ID=(SELECT MARKING_PERIOD_ID FROM course_periods
WHERE COURSE_PERIOD_ID='237'))
AND (SELECT END_DATE
FROM school_marking_periods
WHERE MARKING_PERIOD_ID=(SELECT MARKING_PERIOD_ID FROM course_periods
WHERE COURSE_PERIOD_ID='237'))
AND NOT EXISTS(SELECT 1
FROM attendance_completed ac2
WHERE ac2.STAFF_ID='60'
AND ac2.PERIOD_ID='49'
AND ac2.SCHOOL_DATE=ac.SCHOOL_DATE
AND ac2.TABLE_NAME=ac.TABLE_NAME)
SQL query
