D.R. D.R. - 1 month ago 6
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

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'
Comments