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?
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
);
I'm testing this code now and it seems that it's still running. The real data from the
posts
table is about millions.@illumillukilluallukalluto . . . Kill the code! The
FROM
clause should not have been in the query.Killed it already. Should I stick to my previous syntax instead?
@illumillukilluallukalluto . . . No. I fixed the query. It shouldn't have a
FROM
clause. That is the point of this version.@illumillukilluallukalluto . . .It had a
FROM
clause which is now removed. That is why it did not complete.