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
