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
WHERE t.origin = 'COMMENT'
You can't specify target table 't' for update in FROM clause
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'