Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Deleting rows found with left join on SQL

I am using this query to show all the departments and their managers, including the departemnts with no managers, that’s why I am using left join. Now I need to delete the departements where there is no manager. This is my current query:

select 
    d.DEPARTMENT_NAME,
    concat(e.FIRST_NAME,"",e.LAST_NAME) as NAME,
    l.CITY 
from locations l
join departments d on d.LOCATION_ID = l.LOCATION_ID 
left join employees e on e.DEPARTMENT_ID = d.DEPARTMENT_ID 
where city = 'Seattle';

How can I change it?

I assume I need to use a delete command somewhere, but I am not sure where exactly

Tags:

2

Answers


  1. 0

    I used a subquery to select LOCATION_ID from locations for ‘Seattle’, that I used to restrict the deletion to departments in that city.

    DELETE d FROM departments d
LEFT JOIN employees e ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.DEPARTMENT_ID IS NULL
AND d.LOCATION_ID IN (
    SELECT l.LOCATION_ID
    FROM locations l
    WHERE l.CITY = 'Seattle'
);
    Login or Signup to reply.
  2. 0 
    DELETE d.*
FROM departments d
JOIN locations l USING (location_id)
WHERE l.city = 'Seattle'
  AND NOT EXISTS (
    SELECT NULL
    FROM employees e
    WHERE e.department_id = d.department_id 
    )

    or

    DELETE d.*
FROM departments d
JOIN locations l USING (location_id)
LEFT JOIN employees e USING (department_id)
WHERE l.city = 'Seattle'
  AND e.department_id IS NULL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search