...

View Full Version : Delete Help Needed



rfresh
07-24-2011, 01:52 AM
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

vinyl-junkie
07-24-2011, 04:12 AM
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
07-25-2011, 03:08 AM
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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum