Question 22236 in Mysql Question posted in
The official MySQL documentation can be found here.

Update with same table select query throwing error – Mysql

 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

Tags:

2

Answers


  1. 0 
    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%20days%20back%20while%20writing,select%20as%20your%20update%20criteria.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search