We have a field in the rm (room) table that counts how many employees occupy the room according to the em (employee) table.
We want to create a trigger to update the rm.count_em field when a change is made to the em (employee) table.
It also uses the bl (building) field, fl (floor) field to distinquish which room it is referencing. The bl, fl and rm fields are in both the em and rm tables. The em_id field is also in both tables.
I keep getting an error message that says "Subquery returned more than 1 value". I know it is because the subquery updates all of the records for rm.count_em and apparently it is trying to put it all into the one record that is updated in the em table but I can't figure out how to get it to work.
This is the code I was using:
Any help would be appreciated.
We want to create a trigger to update the rm.count_em field when a change is made to the em (employee) table.
It also uses the bl (building) field, fl (floor) field to distinquish which room it is referencing. The bl, fl and rm fields are in both the em and rm tables. The em_id field is also in both tables.
I keep getting an error message that says "Subquery returned more than 1 value". I know it is because the subquery updates all of the records for rm.count_em and apparently it is trying to put it all into the one record that is updated in the em table but I can't figure out how to get it to work.
This is the code I was using:
Code:
UPDATE rm
SET rm.count_em = (SELECT Count(em_id) AS EmployeeHeadcount
FROM em, rm
WHERE em.bl_id = rm.bl_id AND em.fl_id = rm.fl_id AND em.rm_id = rm.rm_id
GROUP BY rm.bl_id, rm.fl_id, rm.rm_id)
FROM em, rm
WHERE em.bl_id = rm.bl_id AND em.fl_id = rm.fl_id AND em.rm_id = rm.rm_id
Any help would be appreciated.