How to combine UPDATE query with subquery?

Question

In my Mysql database I want to update a field fup for all records which I found using this select statement:

SELECT ic.hash
FROM incompany as ic, app
WHERE ic.id = app.ic_id;

So I created the following combined query:

UPDATE incompany
SET fup = 'x' 
WHERE hash IN (SELECT ic.hash
FROM incompany as ic, app
WHERE ic.id = app.ic_id);

But this query gives me the following error:

You can't specify target table 'incompany' for update in FROM clause

Does anybody know how I can make this work? All tips are welcome!

Possibly a duplicate of: https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause#14302701 — hage, Nov 13 '17 at 14:42

score 2 · Accepted Answer · answered Nov 13 '17 at 14:49

You seem to want a condition on two columns, so this is a bit tricky. If I follow the logic correctly:

UPDATE incompany ic JOIN
       (SELECT DISTINCT ic.hash
        FROM incompany ic JOIN
             app
             ON ic.id = app.ic_id 
       ) ica
       ON ica.hash = ic.hash
    SET fup = 'x' ;

kiks73 · Answer 2 · 2017-11-13T14:51:09.527

1

You do not need a subquery. You might use INNER JOIN to set the criteria to link the incompany table to the app table and to the another alias for incompany table.

UPDATE incompany I 
INNER JOIN app A ON I.id = A.ic_id
INNER JOIN incompany I2 ON I.hash = I2.hash
SET I.fup = 'x'

edited Nov 13 '17 at 14:51

answered Nov 13 '17 at 14:45

kiks73

3,718
3
25
52

How to combine UPDATE query with subquery?

2 Answers2