We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

MySQL / Cloudscape

pete420
pete420 asked
on
Medium Priority
320 Views
Last Modified: 2007-12-19
Hi,

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.

Thanks,
Pete
Comment
Watch Question

How about...

UPDATE HCStaff
INNER JOIN (SELECT COUNT(hcsID) total, HCSID FROM patient GROUP BY HSID) tblX ON (HCStaff.HCSID = tblX.HCSID)
SET HCStaff.total_patients = tblX.total

Author

Commented:
Hi,

Thanks for the quick reply.

First off, just notices I posted in the MySQL section by mistake. Meant to post in a more general area as this is JDBC/sql


I tried your solution above but cview is complaining about the word INNER. Strange as it does support inner joins.


Huh.  You might try it without the "INNER" (JOIN by itself is sufficient).

If its problem is it doesn't like doing an UPDATE with a JOIN involved, then a different solution may be in order.  I wonder if the following would work:

UPDATE HCStaff SET total_patients = (SELECT COUNT(hcsID) FROM patient WHERE HSID = HCStaff.HCSID);

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Hi,

I had already tried it without INNER with no success.

However, your second solution is spot on.

Thanks very much!


Pete
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.