Chuck Doucette Chuck Doucette - 1 year ago 69
MySQL Question

Can you improve a mysql update with two embedded selects with joins?

I have 3 tables used in this query:

  1. instructor (contains a "soft" deleted column)

  2. school (contains a "soft" deleted column)

  3. instructor_school (association table between instructor and school)

If a school is marked as deleted, I want the instructors at that school to also be marked as deleted, but only if all of their schools are marked as deleted. Here's my proposed query:

update instructor as i
set deleted=1
(select count(*) from instructor_school as i_s join school s
where =
(select count(*) from instructor_school as i_s join school s
where and s.deleted=1);

If we only allowed each instructor to be in a single school, the query would be much simpler:

UPDATE instructor i
JOIN instructor_school as i_s ON i_s.instructor_id =
JOIN school as s on
SET i.deleted = 1
where s.deleted = 1

My question is - is this query the best approach to solve this problem - or can you suggest a better query?

Thanks for any suggestions!

Answer Source
UPDATE instructor AS i
JOIN instructor_school i_s_deleted ON = i_s_deleted.instructor_id
JOIN school s_deleted ON i_s_deleted.school_id = AND s_deleted.deleted = 1
LEFT JOIN instructor_school AS i_s_active 
    JOIN school s_active ON i_s_active.school_id = AND s_active.deleted <> 1
ON = i_s_active.instructor_id 
SET i.deleted=1
WHERE i_s_active.school_id IS NULL;

What I did here was first I joined to instructor_school to and to school to make sure that there is a school that is deleted. Then I left joined to instructor_school and school again as active and then I put where i_s_active.school_id is null into my where clause. This will ensure that the instructor is not associated to any active schools.

Hope this helps!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download