Delete Help Needed

I have the following SELECT query statement which works fine. What I want to do now is turn it into a DELETE statement. But when I replace SELECT with DELETE, I get an error. I've tried removing the DISTINCT but I still can't get it to delete.

Thanks for any help.


SELECT DISTINCT E.*, COUNT(ED.id) AS dcnt FROM Employees E LEFT JOIN EmpDepts ED ON E.id = ED.emp_id WHERE E.company_index = 78 AND E.employee = 0 GROUP BY E.last_name HAVING dcnt = 0

I believe you can do something like this:

DELETE FROM EMPLOYEES WHERE Employees.id IN
(
SELECT DISTINCT E.*, COUNT(ED.id) AS dcnt
FROM Employees E
LEFT JOIN EmpDepts ED ON E.id = ED.emp_id
WHERE E.company_index = 78
AND E.employee = 0
GROUP BY E.last_name HAVING dcnt = 0
)

I don't think so.

When you do ".... WHERE somefield IN ( SELECT xxx FROM yyy ) ...", the SELECT must return *ONLY* ONE FIELD. To wit, the field that matches the somefield field value.

So, *PROBABLY* more like

DELETE FROM Employees
WHERE id NOT IN ( SELECT emp_id FROM EmpDepts )
AND company_index = 78
AND employee = 0


No?