Update with same table select query throwing error-CodePudding

 UPDATE
   work_info as info1
 SET
   info1.status_id = 1
  WHERE
 info1.info_id IN(
    SELECT info2.info_id
    FROM work_info as info2
      WHERE info2.info_id IN (
        SELECT MAX(info3.info_id)
          FROM work_info as info3
       GROUP BY info3.user_license_id)
    AND info2.status_id = 5)

Getting this error #1093 - You can't specify target table 'info1' for update in FROM clause

CodePudding user response：

Directly (without trying to understand the logic of the query):

UPDATE work_info as info1
JOIN work_info as info2 USING (info_id)
JOIN ( SELECT MAX(info_id) info_id
       FROM work_info
       GROUP BY user_license_id 
       ) as info3 USING (info_id)
SET info1.status_id = 1
WHERE info2.status_id = 5;

CodePudding user response：

UPDATE table SET a=value WHERE x IN (SELECT x FROM table WHERE condition); because it is the same table, you can trick and do :

UPDATE table SET a=value WHERE x IN (SELECT * FROM (SELECT x FROM table WHERE condition) as t) [update or delete or whatever]

https://www.codeproject.com/Tips/831164/MySQL-can-t-specify-target-table-for-update-in-FRO#:~:text=Some days back while writing,select as your update criteria.