Update syntax

Analytics
Enthusiast

Update syntax

I can run below query in DB2, but I got the error message in Teradata. May anyone can help me to solve it?

UPDATE TMP1
FROM TEMP_PRD.T_CST_SUB_DAILY_INFO_TEMP TMP1
SET (TMP1.T_NO_OF_ACTIVE_MRT ) =
( SELECT COUNT (*)
FROM TEMP_PRD.T_CST_SUB_DAILY_INFO_TEMP TMP2
WHERE TMP2.T_CUA_CUST_CODE = TMP1.T_CUA_CUST_CODE
AND TMP2.T_SUB_STATUS IN ('A','O','S')
);
1 REPLY
Enthusiast

Re: Update syntax

Something like this would probably do ...

UPDATE TMP1
FROM TEMP_PRD.T_CST_SUB_DAILY_INFO_TEMP TMP1
,(SELECT T_CUA_CUST_CODE
, COUNT (*) AS CC_COUNT
FROM TEMP_PRD.T_CST_SUB_DAILY_INFO_TEMP TMP2
WHERE TMP2.T_SUB_STATUS IN ('A','O','S')
GROUP BY 1) TMP2

SET T_NO_OF_ACTIVE_MRT = TMP2.CC_COUNT
WHERE TMP2.T_CUA_CUST_CODE = TMP1.T_CUA_CUST_CODE;

(doesn't look very elegant though - there must be a better way).

If the table is large, there'll be an awful lot of redundant bytes being carried around - each row for a particular T_CUA_CUST_CODE will have the same count value. So if you have a million rows for a particular T_CUA_CUST_CODE with T_SUB_STATUS = 'A', you'll have T_NO_OF_ACTIVE_MRT set to 1000000 on each of them :-( Perhaps you should set up a different table that could describe the attributes of each individual Cust Code ?

Good Luck !