Select For Update statement in PostgreSql

Question

I want to select fields in a record of a table and update just one of these fields. How can I do?

I try this:

SELECT v.idvideo, v.title 
FROM video v  WHERE v.schedulingflag IS FALSE AND v.errorflag IS FALSE 
ORDER BY v.idvideo  LIMIT 1 FOR UPDATE ;

UPDATE video  SET schedulingflag = true;

But in this way it sets field "schedulingflag" true in all record!

Tombart · Accepted Answer · 2016-04-18 22:04:54Z

16

The SELECT FOR UPDATE syntax tells PG that you're going to be updating those records and locks them against concurrent access. However you still need to issue the appropriate UPDATE call to change the particular records you've locked.

In this case, just use the same WHERE clause in your UPDATE, e.g:

UPDATE video  SET schedulingflag = true 
WHERE schedulingflag IS FALSE AND errorflag IS FALSE;

edited Apr 18, 2016 at 22:04

Tombart

32.9k16 gold badges135 silver badges149 bronze badges

answered Feb 5, 2013 at 14:53

Dave S.

6,42934 silver badges36 bronze badges

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

2 Comments

Emanuele Mazzoni Over a year ago

Hi, i want to lock the table, can you see my previous question? stackoverflow.com/questions/14707002/…

Rahly Over a year ago

Note, that he's using a LIMIT 1 for the FOR UPDATE, this generally means that his where clause may return more than 1 record. Now, since I don't have his DDL for the table, he should use whatever his primary key is to do the update, if its idvideo, then he should use UPDATE video SET scheduleflag=true WHERE idvideo=?. The reason being is that even if he uses the same query (ORDER BY/LIMIT), he could STILL update a different record (a new id could be inserted before it)

Collectives™ on Stack Overflow

Select For Update statement in PostgreSql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related