Обсуждение: Updating tuples based on max time

I keep crashing my backend doing the following
UPDATE logins SET
    logoff_time='now'
WHERE
    login_time=max(login_time)
AND
    account_name='tempuser'
;

login_time is type 'datetime'

As a side note, I used this method because the following didn't work
UPDATE logins SET
    logoff_time='now'
WHERE
    id=(SELECT DISTINCT id FROM logins
            WHERE
                login_time IS NOT NULL
            AND
                account_name='tempuser'
        )
;

Point is, what is the best method to update a record based on it's
datetime being the most recent?

6.4 will have the HAVING clause, that will testing of aggregates.

6.4 beta is September 1.

> I keep crashing my backend doing the following
> UPDATE logins SET
>     logoff_time='now'
> WHERE
>     login_time=max(login_time)
> AND
>     account_name='tempuser'
> ;
>
> login_time is type 'datetime'
>
> As a side note, I used this method because the following didn't work
> UPDATE logins SET
>     logoff_time='now'
> WHERE
>     id=(SELECT DISTINCT id FROM logins
>             WHERE
>                 login_time IS NOT NULL
>             AND
>                 account_name='tempuser'
>         )
> ;
>
> Point is, what is the best method to update a record based on it's
> datetime being the most recent?
>
>
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)