The query below works pefectly in SQL Server
update userrole
set userid = 6 where userid = 5
and id not in
(
select b.id from userrole a inner join userrole b
on a.roleid = b.roleid and a.region = b.region
where a.userid = 6 and b.userid = 5
)
When I use the same query in MYSQL, I get the exception 1093 which says Error Code: 1093. You can't specify target table 'userrole' for update in FROM clause.
- How to change the query to MySQL equivalent?
- How to use JOIN query instead of sub-query in the above query
Thanks in advance.