Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    33

    Unanswered: Error in Update Query

    Hi ,
    I'm trying to run the following query but I'm getting error . Could you please help me identifying what is wrong with the query.

    Code:
    update EBI.BKP_MLY_GL_BAL_FCT X
    SET X.PTCTR_I=Y.PTCTR_I,X.UPDT_TS = Current timestamp
    from
    X Inner join 
    (SELECT 
    A.GL_ACCT_I,
    B.PTCTR_I,
    A.CTCTR_I,
    A.ACCT_MO_END_D,
    A.GL_BAL_TYPE_I,
    A.TP_LGL_CO_I,
    A.LGL_CO_I FROM
    EBI.BKP_MLY_GL_BAL_FCT A,
    EBI.CTCTR_DIM_H B ,
    EBI.GL_ACCT_DIM C,
    EBI.GL_BAL_TYPE_DIM D
    WHERE A.GL_ACCT_I=C.GL_ACCT_I AND 
    A.GL_BAL_TYPE_I=D.GL_BAL_TYPE_I AND 
    A.LGL_CO_I=B.LGL_CO_I AND 
    A.CTCTR_I=B.CTCTR_I AND 
    A.ACCT_MO_END_D > '2012-02-01' AND
    A.ACCT_MO_END_D < '2012-12-31' AND 
    A.CTCTR_I<>-1 AND 
    A.BDGT_RATE_NET_A <>0 AND
    A.ACCT_MO_END_D BETWEEN B.EFF_D AND B.EXPR_D
    AND A.PTCTR_I <> B.PTCTR_I WITH UR) Y
    ON
    X.GL_ACCT_I = Y.GL_ACCT_I
    AND X.ACCT_MO_END_D = Y.ACCT_MO_END_D 
    AND X.CTCTR_I=Y.CTCTR_I 
    AND X.LGL_CO_I=Y.LGL_CO_I 
    AND X.GL_BAL_TYPE_I=Y.GL_BAL_TYPE_I 
    AND X.TP_LGL_CO_I=Y.TP_LGL_CO_I 
    AND X.PTCTR_I <> Y.PTCTR_I
    I'm getting following error.

    Code:
    update EBI.BKP_MLY_GL_BAL_FCT X SET X.PTCTR_I=Y.PTCTR_I,X.UPDT_TS = Current timestamp from X Inner join (SELECT A.GL_ACCT_I,B.PTCTR_I,A.CTCTR_I,A.ACCT_MO_END_D,A.GL_BAL_TYPE_I,A.TP_LGL_CO_I,A.LGL_CO_I FROM EBI.BKP_MLY_GL_BAL_FCT A, EBI.CTCTR_DIM_H B ,EBI.GL_ACCT_DIM C,EBI.GL_BAL_TYPE_DIM D WHERE A.GL_ACCT_I=C.GL_ACCT_I AND A.GL_BAL_TYPE_I=D.GL_BAL_TYPE_I AND A.LGL_CO_I=B.LGL_CO_I AND A.CTCTR_I=B.CTCTR_I AND A.ACCT_MO_END_D > '2012-02-01' and A.ACCT_MO_END_D < '2012-12-31' AND A.CTCTR_I<>-1 AND A.BDGT_RATE_NET_A <>0 AND A.ACCT_MO_END_D BETWEEN B.EFF_D AND B.EXPR_D AND A.PTCTR_I <> B.PTCTR_I WITH UR) Y ON X.GL_ACCT_I = Y.GL_ACCT_I AND X.ACCT_MO_END_D = Y.ACCT_MO_END_D AND X.CTCTR_I=Y.CTCTR_I AND X.LGL_CO_I=Y.LGL_CO_I AND X.GL_BAL_TYPE_I=Y.GL_BAL_TYPE_I AND X.TP_LGL_CO_I=Y.TP_LGL_CO_I AND X.PTCTR_I <> Y.PTCTR_I
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "from X Inner join (SELECT A.GL_ACCT_I,B.PT" was 
    found following "= Current timestamp".  Expected tokens may include:  
    "<space>".  SQLSTATE=42601
    
    SQL0104N  An unexpected token "from X Inner join (SELECT A.GL_ACCT_I,B.PT" was found following "= Current timestamp".  Expected tokens may include:  "<space>".
    
    Explanation: 
    
    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by anjan.k View Post
    Could you please help me identifying what is wrong with the query.
    Certainly. There should be no FROM clause in an UPDATE statement. It should look like:

    Code:
    UPDATE <table> SET <column> = <expression> WHERE <search condition>
    You can place a subselect in the WHERE clause.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •