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