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
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts

    newb conditional update question

    hello

    I've painfully written the following query which works

    Code:
    SELECT MemNo, Title, FirstName, LastName, Company
    FROM All_Members
    WHERE Company
    IN (SELECT Company
    FROM All_Members
    WHERE Corporate = 'y'
    GROUP BY Company
    HAVING count( * ) < 4
    ) ORDER BY Company
    I now need to be able to run an UPDATE on the results returned by that query, my fingers and toes MYSQL knowledge runs out at this point

    This, please don't laugh, is supposed to clear the corporate field for the results returned
    Code:
    UPDATE All_Members SET Corporate = ''
    WHERE Company
    IN (SELECT Company
    FROM All_Members
    WHERE Corporate = 'y'
    GROUP BY Company
    HAVING count( * ) < 4
    )
    and gives me the following error

    Code:
    Error
    
    SQL query: Documentation
    
    UPDATE All_Members SET Corporate = '' WHERE Company IN (
    SELECT Company
    FROM All_Members
    WHERE Corporate = 'y'
    GROUP BY Company
    HAVING count( * ) <4
    )
    
    MySQL said: Documentation
    #1093 - You can't specify target table 'All_Members' for update in FROM clause
    I'd be stunned if there's only one problem in there

    I'm on MYSQL version: 4.1.21

    Thanks for your time and apologies for any vague or dodgy anythings

  • #2
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    after some weary googling on updates using subqueries, I got that UPDATE query to work by using a backup of the table to do the query against

    that seems to have worked, so good

    I assume that there's a way to run it on a table all in one go though?

    And it's a 133 second query on a 1100 record table, that's a slow and inefficient query I take it?

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    You can't update a table you are selecting records from.

    Also note that your GROUP BY clause is incorrect. When you use a GROUP BY clause it needs to contain all the columns that are in your SELECT statement.


  •  

    Posting Permissions

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