MYSQL Set Update on same table-CodePudding

I have the following mysql query in attempt to update the owner of identifiers of an account (139) with the same owner for identifiers on any other account that already exists in the same table as follows:

update portfolio_identifier
SET owner_id = owner_id
where
account_id = 139
and code in(select code from portfolio_identifier where portfolio_identifier.account_id <> 139);

However my query does not work as am getting the following error:

Error Code: 1093. You can't specify target table 'portfolio_identifier' for update in FROM clause

How can this be done if possible? Thanks

CodePudding user response：

We can use an update join here:

UPDATE portfolio_identifier p1
INNER JOIN portfolio_identifier p2
ON p1.code = p2.code
SET p1.owner_id = p2.owner_id
WHERE p1.account_id = 139 AND
      p2.account_id <> 139;