Deleting rows found with left join on SQL

Question

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

what identifies a manager?

ysth
– ysth

2024-03-11 04:56:41 +00:00
Commented Mar 11, 2024 at 4:56 — ysth
– ysth, Commented Mar 11, 2024 at 4:56

user9517769 · Accepted Answer · 2024-03-10 21:09:12Z

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'
);

answered Mar 10, 2024 at 21:09

user9517769

Sign up to request clarification or add additional context in comments.

Comments

Akina · Accepted Answer · 2024-03-11 04:44:34Z

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

answered Mar 11, 2024 at 4:44

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Deleting rows found with left join on SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related