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

Question

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?

@GordonLinoff, it's just a function to transform the data into a formatted text. — illumillukilluallukalluto
– illumillukilluallukalluto, Commented Nov 28, 2020 at 13:27

Gordon Linoff · Accepted Answer · 2020-11-28 13:39:16Z

1

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
                  );

edited Nov 28, 2020 at 13:39

answered Nov 28, 2020 at 13:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

illumillukilluallukalluto Over a year ago

I'm testing this code now and it seems that it's still running. The real data from the posts table is about millions.

Gordon Linoff Over a year ago

@illumillukilluallukalluto . . . Kill the code! The FROM clause should not have been in the query.

illumillukilluallukalluto Over a year ago

Killed it already. Should I stick to my previous syntax instead?

Gordon Linoff Over a year ago

@illumillukilluallukalluto . . . No. I fixed the query. It shouldn't have a FROM clause. That is the point of this version.

Gordon Linoff Over a year ago

@illumillukilluallukalluto . . .It had a FROM clause which is now removed. That is why it did not complete.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related