updating with max value - Mailing list pgsql-general

not sure if i'm being stupid...

Here are the tables:

test=# \d order_head
                       Table "order_head"
      Attribute      |     Type      |         Modifier
---------------------+---------------+--------------------------
 order_head_id       | integer       | not null
 order_edited        | date          |

test=# \d order_head_hist
            Table "order_head_hist"
      Attribute      |     Type      | Modifier
---------------------+---------------+----------
 order_head_id       | integer       |
 audit_date          | timestamp     |
 audit_action        | varchar(10)   |

Some of the audit data:

test=# select order_head_id, audit_date from order_head_hist where
audit_action = 'update';
 order_head_id |       audit_date
---------------+------------------------
          1591 | 2001-04-26 14:38:05+01
          1589 | 2001-04-26 14:38:05+01
          1589 | 2001-04-26 14:38:05+01
          1590 | 2001-04-26 14:38:05+01
          1590 | 2001-04-26 14:38:05+01
          1591 | 2001-04-26 14:38:05+01
          1591 | 2001-04-26 14:38:05+01
          1591 | 2001-04-26 14:38:05+01

But when I do this:

test=# UPDATE order_head SET
test-#  order_edited = max(order_head_hist.audit_date)
test-# FROM order_head_hist
test-# WHERE order_head.order_head_id = order_head_hist.order_head_id
test-# AND order_head_hist.audit_action = 'update' ;
UPDATE 1
test=#

It only updates one row:

test=# select order_head_id, order_edited from order_head;
 order_head_id | order_edited
---------------+--------------
          1590 |
          1591 |
          1589 | 2001-04-26

I'm guessing its just updating the row with the max audit_date in the whole
order_head_hist table, rather than grouping by order_head_id, but i can't
seem to add a group by.  Is there a way round this or am i missing something
obvious...?

thanks for any help
tamsin

PostgreSQL 7.0.2