1

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?

2
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.

  • thanks that worked great. Can you tell me how I know when brackets are needed and when they're not? I'm newish to databases and I often find I have them when I shouldn't and vice versa. For example for an insert into...select there should be no brackets. What's the difference? – Fawn Apr 22 '15 at 19:34
  • @Fawn: This one is a sub-select, which always requires parentheses. The INSERT statement accepts a query as source in standard syntax. If in doubt, consult the SQL command reference. – Erwin Brandstetter Apr 22 '15 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.