update statement with sub query.

Database

update statement with sub query.

hi need small help regarding the update,

when i am execute this statement , getting the error

Update VM20012_KPI set ASD =
(SELECT SUM(SECT_CMPT_SLBL_CAP) FROM VM20008_SECTOR M20008,VM20012_KPI KPI
WHERE M20008.DEPUPOR = KPI.PORT AND
M20008.LOCLDEPD = KPI.KPI_DATE);

syntax error: expected something between '(' and the 'SELECT' keyword.

can u help me in fixing this.
1 REPLY
Enthusiast

Re: update statement with sub query.

Hi lokesh,

Try this:

Update VM20012_KPI
From
(
SELECT SUM(SECT_CMPT_SLBL_CAP) sect
FROM VM20008_SECTOR M20008,VM20012_KPI KPI
WHERE M20008.DEPUPOR = KPI.PORT
AND
M20008.LOCLDEPD = KPI.KPI_DATE)dt

Set ASD =dt.sect

While taking input value from a subquery/derived table ,we have to follow the above syntax in teradata (as per my knowledge).
If you you are not using any value from the subquery/derived table then the above syntax is not needed

eg:

Update table_b
set col_b='10' /*input value is hardcoded here,not taken from subquery */
where col_b=(sel col_a from table_a)

Cheers:-)