CODEWITHSUNDEEP
mysql
deepu sankar
deepu sankar

Reputation: 4455

Update with same table select query throwing error

 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

Upvotes: 0

Views: 33

Answers (2)

Nahume
Nahume

Reputation: 31

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.

Upvotes: 0

Akina
Akina

Reputation: 42632

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;

Upvotes: 2

Related Questions