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 )
) )
) )
)
) ) )

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.....

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.

Is it possible that accounts is a MySQL reserved word ?

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

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'