D.R. D.R. - 1 year ago 70
MySQL Question

UPDATE with WHERE in sub select

I have a flat table containing posts and comments. Currently posts and comments are not really connected to each other, I only know, that comments reside immediately before (in terms of the id column) their corresponding post.

The first step to improvement is to create a "commentforid" column and fill it appropriately:

UPDATE mytable t
SET t.commentforid = (SELECT x.id
FROM mytable x
WHERE x.origin = 'POST'
AND x.id > t.id
LIMIT 1)
WHERE t.origin = 'COMMENT'


However, the query fails with


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


Does anyone know what the problem is? I can't make anything out of the error message.

Answer Source

The error is related to the fact you are updating the same table you are selecting you could override this situation using a temp table

Try using a dinamic from table

  UPDATE mytable t
  SET t.commentforid = (SELECT x.id

                        FROM ( select z.id from mytable z
                        WHERE x.origin = 'POST' ) x on  x.id > t.id
                        LIMIT 1)
  WHERE t.origin = 'COMMENT'