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!
Advertisement
Answer
Try this:
x
UPDATE TABLE_A ASET 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) BWHERE A.a_id = B.table_a_fk