Multiple Updates in a Table A with Max(date) from Table B 1

[DFW1999]

Hello,

I need to update three fields of Table_A with the two fields of Table_B and with one constant 'M'.

I have the following query to update three fields in table_A:

UPDATE TABLE_A
SET CREATE_TIMESTAMP=(SELECT MAX(CREATE_TIMESTAMP) FROM TABLE_B
SET UPDATE_TIMESTAMP=(SELECT MAX(UPDATE_TIMESTAMP) FROM TABLE_B
SET DATA_DATE=(SELECT MAX(DATA_DATE) FROM TABLE_B
SET ACTION_CODE='M';

I am getting follwoing errors:
" something needs to be between ( and SLECET "
AND
"Aggregation is not allowed in this update"

Any suggestions ?

thanks

 

[dnoeth]

1. There's only 1 SET
2. You can't use scalar subqueries in Teradata SQL

Untested:

UPDATE TABLE_A
FROM
(
SELECT
MAX(CREATE_TIMESTAMP) as maxCreate,
MAX(UPDATE_TIMESTAMP) as maxUpd,
MAX(DATA_DATE) as maxDate
FROM TABLE_B
) dt
SET
CREATE_TIMESTAMP=maxCreate,
UPDATE_TIMESTAMP=maxUpd,
DATA_DATE=maxDate
;


Dieter

 

[DFW1999]

Just an updated/revised query for multiple updates to a table A wiht max values of table B.

Still getting max aggregated functions is not allowed here.


UPDATE TABLE_A
FROM TABLE_A , TABLE_B
SET CREATE_TIMESTAMP = max(TABLE_B.CREATE_TIMESTAMP)
,UPDATE_TIMESTAMP = max(TABLE_B.UPDATE_TIMESTAMP)
,ACTION_CODE='M'
WHERE
TABLE_A.colA = TABLE_B.colA

Any suggestions would be appreciated.

 

[DFW1999]

Thanks Dieter good job!!!