Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    545
    Thanks
    81
    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.

    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 
    RalphF
    Business Text Messaging Services
    https://www.MobileTextingService.com

  • #2
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,090
    Thanks
    2
    Thanked 23 Times in 23 Posts
    I believe you can do something like this:

    Code:
    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  
    )
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • Users who have thanked vinyl-junkie for this post:

    rfresh (07-24-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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
    Code:
    DELETE FROM Employees 
    WHERE id NOT IN ( SELECT emp_id FROM EmpDepts )
    AND company_index = 78 
    AND employee = 0
    No?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •