Update only the first row from the result of a where clause in Postgresql-CodePudding

I'm trying to only update the first row that I get from the resulting where clause. The following WHERE gets me the rows within a certain time frame, I really only want to update the one which is closest in time which would be the first row.

I've tried using Order by with LIMIT 1 and Top 1 but it seems that these do not work in Postgresql. What's the best way of going about this?

UPDATE data 
SET value = '123' 
WHERE (ttime - '18:27:21.4') <= '2' and (ttime - '18:27:21.4') >= '0')

CodePudding user response：

Safest way is to use some unique identifier and build query like this

UPDATE data 
SET value = '123' 
WHERE <some_unique_id> in (
  select <some_unique_id> from data 
  where (ttime - '18:27:21.4') <= '2' and (ttime - '18:27:21.4') >= '0'
  order by <whatever>
  limit <rows to update>
)

If there is no unique identifier but unique key containing multiple fields, it's still possible to do something like this

UPDATE data 
SET value = '123' 
WHERE (<field1>,<field2>) in (
  select <field1>,<field2> from data 
  where (ttime - '18:27:21.4') <= '2' and (ttime - '18:27:21.4') >= '0'
  order by <whatever>
  limit <rows to update>
)