Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    34

    Unanswered: Error in UPDATE statement

    I have to update 2 coulmns in table1 from table2
    where their primary key values are equal
    I wrote the code as below

    UPDATE DWSODS01.DWT00102_DERV_IMC_MISC
    Set DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO = (SELECT INMKT_LAST_SPON_DT_KEY_NO FROM DWT00002_IMC_DMS_MAIN WHERE DWT00102_DERV_IMC_MISC.IMC_KEY_NO = DWT00002_IMC_DMS_MAIN.IMC_KEY_NO
    ),
    DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO =
    (CASE WHEN ((DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO = 19000101) or (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO is null)) then
    (SELECT INMKT_FIRST_SPON_DT_KEY_NO FROM DWT00002_IMC_DMS_MAIN)
    else
    (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO)
    END WHERE DWT00102_DERV_IMC_MISC.IMC_KEY_NO = DWT00002_IMC_DMS_MAIN.IMC_KEY_NO
    )

    When i run this code i am getting an error
    "Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 - "missing right parenthesis"
    *Cause:
    *Action:"


    Can anyone say where is the error

  2. #2
    Join Date
    Nov 2009
    Posts
    34
    I tried this also


    UPDATE DWSODS01.DWT00102_DERV_IMC_MISC
    Set DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO = (SELECT INMKT_LAST_SPON_DT_KEY_NO FROM DWT00002_IMC_DMS_MAIN WHERE DWT00102_DERV_IMC_MISC.IMC_KEY_NO = DWT00002_IMC_DMS_MAIN.IMC_KEY_NO
    ),
    DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO =
    (CASE WHEN ((DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO = 19000101) or (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO is null)) then
    (SELECT INMKT_FIRST_SPON_DT_KEY_NO FROM DWT00002_IMC_DMS_MAIN)
    else
    (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO)
    END)
    WHERE DWT00102_DERV_IMC_MISC.IMC_KEY_NO = DWT00002_IMC_DMS_MAIN.IMC_KEY_NO

    But when i run i am getting error message

    "Error report:
    SQL Error: ORA-00904: "DWT00002_IMC_DMS_MAIN"."IMC_KEY_NO": invalid identifier
    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:"

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think you had the final WHERE clause in the wrong place. Try:
    Code:
    UPDATE DWSODS01.DWT00102_DERV_IMC_MISC
    Set DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO = 
     (SELECT INMKT_LAST_SPON_DT_KEY_NO 
      FROM DWT00002_IMC_DMS_MAIN 
      WHERE DWT00102_DERV_IMC_MISC.IMC_KEY_NO = DWT00002_IMC_DMS_MAIN.IMC_KEY_NO
     ),
    DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO =
     CASE WHEN (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO = 19000101) or (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO is null)
     then (SELECT INMKT_FIRST_SPON_DT_KEY_NO 
           FROM DWT00002_IMC_DMS_MAIN 
           WHERE DWT00102_DERV_IMC_MISC.IMC_KEY_NO = DWT00002_IMC_DMS_MAIN.IMC_KEY_NO
          )
     else DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO
     END

  4. #4
    Join Date
    Nov 2009
    Posts
    34
    Thanks for taking interest in my problem

    I hope this is working

    Thanks again

Posting Permissions

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