I would like to run a trigger in cview on cloudscape but am having some difficulty in writing the correct sql.
I have a table 'patient' which has a field 'hcsID'. In this field there is an int (either 1,2,3,4,5). This defines which staff is dealing with which patient.
In the 'HCStaff' table there is a field 'total_patients'. I want this field to be updated depending on what value is in hcsID in the patient table.
my attempt so far is:
update HCStaff set total_patients = select count(hcsID) from patient group by HCSID
I think I have the right idea but cannot further this.
Any help is appreciated.