Update specific column within the same table and add limit – PostgreSQL

I would like to update the value of a column within the same table by transforming its former value, but I would like to first test it with the first 10 data according to its created date.

I am getting this error though

ERROR:  syntax error at end of input
LINE 6: limit 10

Here is my sample code:

UPDATE posts p1
SET cooked = (select entity2char(strip_tags(p2.cooked))
FROM posts p2
WHERE p1.id = p2.id
ORDER BY p2.created_at ASC 
LIMIT 10

Any syntax that would make this work?

Answer

Hmmm . . . I don’t see why you are using a self-join. If you just want to delete the top 10 entities, you can use:

UPDATE posts p
    SET cooked = entity2char(strip_tags(p.cooked))
    WHERE p.id IN (SELECT p2.id
                   FROM posts p2
                   ORDER BY p2.created_at ASC 
                   LIMIT 10
                  );