Mrslipp3ry Mrslipp3ry - 7 months ago 21
SQL Question

Select For Update statement in PostgreSql

I want to select fields in a recor 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!

Answer

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