Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: Delete Help Needed
07-24-2011, 12:52 AM #1
- Join Date
- Jun 2007
- Los Angeles
- Thanked 5 Times in 5 Posts
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
Business Text Messaging Services
07-24-2011, 03:12 AM #2
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 )
Users who have thanked vinyl-junkie for this post:
07-25-2011, 02:08 AM #3
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