Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Unanswered: ERROR 1093 (HY000): You can't specify target table 'Accounts' for update in FROM clau

    Hi All,

    I get the following error message if I run an UPDATE query in MySQL. The error message is as follows:

    "ERROR 1093 (HY000): You can't specify target table 'Accounts' for update in FROM clause".

    The query I am using is as below.

    Help me to find out what is the mistake in this query. But the same query is working fine with MSSQLServer and Oracle.


    I know “In MySQL, we cannot delete/update from a table and select from the same table in a sub query”. I tried to give the ALIAS name using AS "clause" for the tables but that also didn't help.


    Please help me.
    Regards,
    Mari.

    UPDATE
    Accounts
    SET
    Accounts.securityILHint = 0 ,
    Accounts.securityIUHint = 0,
    Accounts.securityGLHint = 0,
    Accounts.securityGUHint = 0,
    Accounts.OrgID = 400001,
    Accounts.AccountType = '',
    Accounts.BODateCreated = '2007-12-04 13:36:46.0',
    Accounts.BOCreatedBy = 1,
    Accounts.BODateModified = 'Thu Dec 06 04:18:30 GMT+05:30 2007',
    Accounts.BOModifiedBy = 1,
    Accounts.ConcurDetect_X = 1,
    Accounts.UserField1 = '',
    Accounts.UserField2 = '',
    Accounts.UserField3 = '',
    Accounts.UserField4 = '',
    Accounts.UserField5 = ''
    WHERE
    Accounts.OrgID = 400001 AND
    (Accounts.OrgID IS NULL OR ( EXISTS (
    SELECT 1
    FROM
    Accounts
    SEC_Accounts , Organization
    WHERE
    ( SEC_Accounts.OrgID = Organization.OrgID AND
    SEC_Accounts.OrgID = Accounts.OrgID AND ( ( Accounts.BOACLID IS NULL OR EXISTS (
    SELECT 1 FROM InstanceACL
    WHERE
    InstanceACL.isgroup = 'N' AND InstanceACL.SID = 1 AND
    InstanceACL.InstanceACLID = SEC_Accounts.BOACLID AND getRightsValue( InstanceACL.rightsLower, 16) = 1 ) OR
    ( NOT EXISTS (
    SELECT 1
    FROM
    InstanceACL
    WHERE
    InstanceACL.isgroup = 'N' AND
    InstanceACL.SID = 1 AND InstanceACL.InstanceACLID = SEC_Accounts.BOACLID AND
    getRightsValue( InstanceACL.rightsLower, 16) = 2 ) AND ( EXISTS (
    SELECT 1
    FROM
    InstanceACL,
    USER_GROUPS WHERE InstanceACL.isGroup='Y' AND
    InstanceACL.Sid = USER_GROUPS.groupID AND user_groups.personID = 1 AND
    InstanceACL.InstanceACLID = SEC_Accounts.BOACLID AND
    getRightsValue( InstanceACL.rightsLower,16) = 1 ) OR NOT EXISTS (
    SELECT 1
    FROM
    InstanceACL,
    USER_GROUPS
    WHERE
    InstanceACL.isGroup='Y' AND
    InstanceACL.Sid = USER_GROUPS.groupID AND
    user_groups.personID = 1 AND InstanceACL.InstanceACLID = SEC_Accounts.BOACLID AND
    getRightsValue( InstanceACL.rightsLower, 16) = 2 )
    ) )
    ) )
    )
    ) ) )

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Far too many not exists...

    Why don't you explain to us in words what you want your query to do.
    Update the accounts table information where.....
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Posts
    4
    Hi George,

    Thanks for looked into the Problem.

    The query is generated by programmatically by a query builder which is doing lot of conditional checking for the specific user based on his access rights. That's why this query is too much lenghtier.

    But the same set of query operation is working for SQLServer and Oracle. I was asked to migrate the application so that the product can support additionally MySQL database also.

    To put it in a simple words I want to know, I can use the UPDATE query which has the subquery in the WHERE clause of the UPDATE query having reference to the same table what my UPDATE query is using. If you provide similar sample also would be very much useful.

    Any suggestion.

    Thanks & Regards,
    Mari.

  4. #4
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Is it possible that accounts is a MySQL reserved word ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  5. #5
    Join Date
    Dec 2007
    Posts
    4
    Thanks for looking into my posted issue.

    The same error is coming for the other tables also such as contacts,agents,users etc., in my application.



    Regards,
    Mari

  6. #6
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    these are all word that may be reserved I don't know if they are... Use the ``

    like this: SELECT * FROM `myTable` WEHRE `myField`='VALUE'
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

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