PostgreSQL 9.3 – Update Row with Max Timestamp Value

postgresql

I want to allow users to cancel recurring subscriptions. The easiest way to do this given the other infrastructure I've got is to set the recurring field = 0 for the user's latest appointment (datewise). So I need to update the value of field recurring only in the row that matches a specific user's latest appointment.

Here's what I tried:

UPDATE appointments SET recurring = 0 WHERE id = 433 AND time = max(time);


ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ...ments set recurring = 0 where id = 433 and time = MAX(time);

and

 UPDATE appointments SET recurring = 0 WHERE id = 433 AND time = SELECT MAX(time) FROM appointments WHERE id = 433;

Here's the relevant part of the appointments table:

                                           Table "public.appointments"
      Column      |            Type             |                           Modifiers
------------------+-----------------------------+----------------------------------------------------------------
 id               | integer                     |
 time             | timestamp with time zone    |
 recurring        | integer                     |

Where should I go from here? Is it necessary to make this into a two part call?

Best Answer

UPDATE appointments SET recurring = 0 
WHERE id = 433 
AND time = (select max(time) from appointments where id = 433);

... is the answer. You just needed brackets.

Related Question