Update using the result of a SELECT

Database
Enthusiast

Update using the result of a SELECT

Can some one help me to know why this update statement fails. Is it possible to use
update using the select statement like this. Your help would be highly appreciated.
###############
update CPU_STAT_MATRIX set TOTAL_CPU_ALL_DAY=(select sum(TotalCpuTime) from DBC.QryLog WHERE CAST(firststeptime as DATE)= (CURRENT_DATE - 1 ) AND To
talCpuTime > 0) WHERE ex_date=CURRENT_DATE-1;
################
Here is the error:

*** Failure 3706 Syntax error: expected something between '(' and the 'sele
ct' keyword.
Statement# 1, Info =55
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:

Krishan.R
1 REPLY
Enthusiast

Re: Update using the result of a SELECT

I am not sure about the table names and the field name that you have but i guess this is how you have to write this query

update cpu_stat_matrix
from (select sum(TotalCpuTime)
from DBC.QryLog WHERE CAST(firststeptime as DATE)= (CURRENT_DATE - 1 )
AND TotalCpuTime > 0) a(total_time)
set total_cpu_all_day = a.total_time
where ex_date=CURRENT_DATE-1;