sql - MySQL触发器更新另一个表中的值

Question

我想要的是在更新/插入表 x 时，将表 y 中的值增加 1

下面的例子，使用了一个 select 语句，当我从我试图更新的表中选择时我不能使用它

DELIMITER $$
create trigger occupancy
after insert on tbl_attendence
for each row
begin
    set @course_id = new.course_id;
    set @attendence_date = new.attendence_date;

    if new.reason = 1 then

        update tbl_course_occupancy
        set occupancy_number= (select occupancy_number 
                                from tbl_course_occupancy 
                               where course_id = @course_id 
                                 and occupancy_year = EXTRACT(year from @attendence_date) ) + 1
        where course_id = @course_id 
          and occupancy_year = Extract(year from @attendence_date);

    end if;

end$$

感谢任何帮助，谢谢

Answer 1

使用:

UPDATE tbl_course_occupancy
   SET occupancy_number = occupancy_number + 1
 WHERE course_id = @course_id 
   AND occupancy_year = Extract(year from @attendence_date);