Delete Help Needed

[rfresh]

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.

PHP Code:

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 


[vinyl-junkie]

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  
)

[Old Pedant]

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?