Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2013
    Posts
    3

    Unanswered: Help with update

    I'm trying to run the following script and will not:

    update COR_DOF
    set VL_ALIQ_PIS = 0.01,
    VL_IMPOSTO_PIS = 1.12
    from COR_DOF B
    JOIN
    COR_IDF A ON A.DOF_SEQUENCE = B.DOF_SEQUENCE AND
    A.CFOP_CODIGO IN ('1.202', '1.411')
    where DOF_IMPORT_NUMERO = 'cef622de-e29b-4f2d-ad4d-1e6ad1c2df66'
    and a.MERC_CODIGO = 259314
    and A.CFOP_CODIGO = '1.202';


    below error:


    Error starting at line 52 in command:
    update COR_DOF
    set VL_ALIQ_PIS = 0.01,
    VL_IMPOSTO_PIS = 1.12
    from COR_DOF B
    JOIN
    COR_IDF A ON A.DOF_SEQUENCE = B.DOF_SEQUENCE AND
    A.CFOP_CODIGO IN ('1.202', '1.411')
    where DOF_IMPORT_NUMERO = 'cef622de-e29b-4f2d-ad4d-1e6ad1c2df66'
    and a.MERC_CODIGO = 259314
    and A.CFOP_CODIGO = '1.202'
    Error at Command Line:55 Column:12
    Error report:
    SQL Error: ORA-00933: SQL command not properly ended
    00933. 00000 - "SQL command not properly ended"
    *Cause:
    *Action:

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could try using Oracle syntax and not SQL server syntax.
    Check it out in the fine Oracle® Database SQL Language Reference.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2013
    Posts
    3
    Quote Originally Posted by LKBrwn_DBA View Post
    You could try using Oracle syntax and not SQL server syntax.
    Check it out in the fine Oracle® Database SQL Language Reference.



    after I checked it and was really wrong syntax, changed and now the other error

    update (
    select
    COR_IDF.VL_IMPOSTO_PIS
    from COR_DOF, COR_IDF
    where
    COR_IDF.DOF_SEQUENCE = COR_DOF.DOF_SEQUENCE
    AND COR_IDF.CFOP_CODIGO IN ('1.202', '1.411')
    and COR_DOF.DOF_IMPORT_NUMERO = 'cef622de-e29b-4f2d-ad4d-1e6ad1c2df66'
    and COR_IDF.MERC_CODIGO = 259314
    and COR_IDF.CFOP_CODIGO = '1.202') TESTE
    set teste.vl_imposto_pis = 1

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    what other error?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I have a basic problem.
    You posted about 10 line of code.
    Error reports problem on Line #55
    Why the discrepancy?

    Which table is actually being updated?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2013
    Posts
    3
    Quote Originally Posted by pablolee View Post
    what other error?


    SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
    01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
    *Cause: An attempt was made to insert or update columns of a join view which
    map to a non-key-preserved table.
    *Action: Modify the underlying base tables directly.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Due to the fact we do not have the table definitions, I am speculating as to the correct update statement.
    Depending on the columns in the conditions, it should look similar to this:
    Code:
    UPDATE cor_dof b
       SET vl_aliq_pis = 0.01, vl_imposto_pis = 1.12
     WHERE dof_import_numero = 'cef622de-e29b-4f2d-ad4d-1e6ad1c2df66'
       AND a.merc_codigo = 259314
       AND a.cfop_codigo = '1.202'
       AND EXISTS
             (SELECT '?'
                FROM cor_idf a
               WHERE a.dof_sequence = b.dof_sequence
                 AND a.cfop_codigo IN ('1.202', '1.411'));
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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