Update Timestamp field with the oldest record of fields within the row or those in a linked table

One table looks like this:

TABLE A	Nullables
a_id	not nullable
create_timestamp	not nullable
edit_timestamp	nullable
closed_timestamp	nullable
last_mod_timestamp	not nullable

The other one like this:

TABLE B	Nullables
b_id	not nullable
table_a_fk	not nullable
create_timestamp	not nullable

How would I set last_mod_timestamp to the oldest value of either (a.create_timestamp, a.edit_timestamp, a.closed_timestamp) or b.create_timestamp where b.table_a_fk = a.id?

Datatypes are all Timestamp.

Thanks in advance!

Answer

Try this:

UPDATE TABLE_A A
SET 
  last_mod_timestamp =
NULLIF
(
  MAX 
  (
    COALESCE (A.create_timestamp, '0001-01-01'::TIMESTAMP)
  , COALESCE (A.edit_timestamp, '0001-01-01'::TIMESTAMP)
  , COALESCE (A.closed_timestamp, '0001-01-01'::TIMESTAMP)
  , COALESCE (B.create_timestamp, '0001-01-01'::TIMESTAMP)
  )
, '0001-01-01'::TIMESTAMP
)
FROM
(
  SELECT table_a_fk, MAX (create_timestamp) create_timestamp
  FROM TABLE_B
  GROUP BY table_a_fk
) B
WHERE A.a_id = B.table_a_fk