Update field with null in postgresql with limit

Question

So I need a query that would null the content in specific field. That's quite easy, but I need it with a limit that I would specify. For selecting the field with LIMIT and FOR UPDATE SKIP LOCKED I know the query. I need something similar but with UPDATE. Here what I've come with:

UPDATE e_message SET message = null FROM (SELECT * FROM e_message where created < :created ORDER BY :created LIMIT :limit FOR UPDATE SKIP LOCKED)

I specify the created and limit variables in java.

Would this work or did I do something wrong?

Laurenz Albe · Accepted Answer · 2020-10-22 14:51:43Z

1

You are almost there:

UPDATE e_message AS e
SET message = null
FROM (SELECT id FROM e_message
      where created < :created
      ORDER BY :created
      LIMIT :limit
      FOR UPDATE SKIP LOCKED) AS e2
WHERE e.id = e2.id;

answered Oct 22, 2020 at 14:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Update field with null in postgresql with limit

1 Answer 1

Comments

Your Answer

Collectives™ on Stack Overflow

Update field with null in postgresql with limit

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related