0

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.

  1. How to change the query to MySQL equivalent?
  2. How to use JOIN query instead of sub-query in the above query

Thanks in advance.

1
  • It's better to post the task, not the possible solution...
    – Akina
    Commented Mar 24, 2020 at 9:59

1 Answer 1

2

If you don 't try to understand the meaning of the request and simply adapt it to MySQL then:

UPDATE userrole ur
LEFT JOIN ( 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 ) sq ON ur.id = sq.id
SET ur.userid = 6 
WHERE ur.userid = 5
WHERE sq.id IS NULL

If you try to understand the task then it looks like (it seems) "alter userid from 5 to 6 if the record with the same (roleid,region) and userid=6 not exists yet". If so the task may be solved by:

UPDATE userrole t1
LEFT JOIN userrole t2 ON (t1.roleid, t1.region) = (t2.roleid, t2.region)
                     AND t2.userid = 6
SET t1.userid = 6
WHERE t1.useris = 5
  AND t2.userid IS NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.