UPDATE Failed. 3706

Database
Enthusiast

UPDATE Failed. 3706

Hi, Please provide suggestions on how to work on this? Thank you.

 

Syntax error: expected something between a string or a Unicode character liter  and the 'FROM' keyword.  

 

SyntaxEditor Code Snippet

UPDATE DATA.TBL

SET BM27_OUT_Z='9999-12-31'

FROM(

SELECT
5 REPLIES
Teradata Employee

Re: UPDATE Failed. 3706

Wrong sequence of clauses - check the DML manual.

Needs to be UPDATE ... FROM ... SET ...

Re: UPDATE Failed. 3706

Hi - My SELECT works fine but giving me error when trying to conver the same to UPDATE as below:

-- SELECT is Working, returns 18 records :

SELECT V.VC_ST_CD
FROM TDX_PRDUSR_TBLS.VISION_CELL_MV V
INNER JOIN TDX_DEVUSR_TBLS.CELL_vp C
ON C.CELL_NO = TO_NUMBER(V.VC_CELL_SITE_NO)
AND TRIM(C.ST_CD) <> TRIM(V.VC_ST_CD)
INNER JOIN TDX_DEV_TBLS.NETWORK_ELEMENT N
ON N.NET_ELEM_NO = C.NET_ELEM_NO
AND N.BILLING_ID = V.VC_ECP_ID
AND V.VC_OUT_OF_SRV_DT > TRUNC(CURRENT_DATE - 30)
AND (N.SWCH_TYPE <> 'LU' OR C.SWCH_NO = 0 OR C.SWCH_NO = TO_NUMBER(V.VC_DCS_NO))
AND V.VC_IN_SVC_DT <= TRUNC(CURRENT_DATE + 8)
AND EXISTS
(SELECT 1 FROM TDX_PRDUSR_TBLS.NPA_STATE R WHERE R.ST_CD = V.VC_ST_CD)
AND V.VC_IN_SVC_DT = (SELECT MAX(VC_IN_SVC_DT) FROM TDX_PRDUSR_TBLS.VISION_CELL_MV V2 WHERE V2.VC_ECP_ID = N.BILLING_ID AND TO_NUMBER(V2.VC_CELL_SITE_NO) = C.CELL_NO)

 

-- Update Isn't working, gives me an error "Column TDX_DEVUSR_TBLS.c.C' doesnt exist":

UPDATE c
FROM TDX_DEVUSR_TBLS.CELL_vp c,
TDX_PRDUSR_TBLS.VISION_CELL_MV V,
TDX_DEV_TBLS.NETWORK_ELEMENT N
SET C.ST_CD = V.VC_ST_CD
WHERE C.CELL_NO = TO_NUMBER(V.VC_CELL_SITE_NO)
AND TRIM(C.ST_CD) <> TRIM(V.VC_ST_CD)
AND N.NET_ELEM_NO = C.NET_ELEM_NO
AND N.BILLING_ID = V.VC_ECP_ID
AND V.VC_OUT_OF_SRV_DT > TRUNC(CURRENT_DATE - 30)
AND (N.SWCH_TYPE <> 'LU' OR C.SWCH_NO = 0 OR C.SWCH_NO = TO_NUMBER(V.VC_DCS_NO))
AND V.VC_IN_SVC_DT <= TRUNC(CURRENT_DATE + 8)
AND EXISTS
(
SELECT 1
FROM TDX_PRDUSR_TBLS.NPA_STATE R WHERE R.ST_CD = V.VC_ST_CD
)
AND V.VC_IN_SVC_DT = (SELECT MAX(VC_IN_SVC_DT) FROM TDX_PRDUSR_TBLS.VISION_CELL_MV V2 WHERE V2.VC_ECP_ID = N.BILLING_ID AND TO_NUMBER(V2.VC_CELL_SITE_NO) = C.CELL_NO);

 

Appreciate your inputs.

Enthusiast

Re: UPDATE Failed. 3706

Thank you, Fred.

 

when i use the given suggestion whith the whole query i got he following error:  UPDATE Failed. 3707:  Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or a 'SELECT' keyword or '(' between '(' and the 'SET' keyword.  

 

Below is the full query.

 

SyntaxEditor Code Snippet

SyntaxEditor Code Snippet
UPDATE data.TBL
from 
(SET BMSY2_Z='9999-12-31'
WHERE 
SELECT BMSD5_D,DSPLANT_C,BASE43_G,PREPARTS_R,RES_S7_G,max(REC_S17_A) MaxReciptQ,MAX(BMSY2_Z) MaxOfBMSY2_Z
FROM data..TBL
WHERE BMSD5_D='FCSDE'

GROUP BY BMSD5_D,DSPLANT_C,BASE43_G,PREPARTS_R,RES_S7_G

HAVING CAST(RES_S7_G as date format 'YYYY-MM-DD' ) = CAST( '2004-08-13' as date format 'YYYY-MM-DD')AND CAST( max(BMSY2_Z) as date format 'YYYY-MM-DD' ) < CAST('9999-12-31' as date format 'YYYY-MM-DD'));

 

Junior Contributor

Re: UPDATE Failed. 3706

You can't qualify the target column in the SET:

SET ST_CD = V.VC_ST_CD

 

Re: UPDATE Failed. 3706

Thank you, it worked.