Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    20

    Unanswered: Oracle ORA-01779 ERROR

    Hy! I tryed this update

    Code:
    UPDATE (select atrp.fid_parcela new_fid_parcela, sdfp.fid old_fid from tb_lm.atribute_parcele atrp, tb_lm.sdf_parcele sdfp where atrp.nr_cadastral=sdfp.nr_cadastral AND 
    atrp.nr_parcela=substr(sdfp.nr_parcela,0,1)) set new_fid_parcela=old_fid
    And I get the ora-01779 error.
    I atached printscreen with the unique constraind and also FID is primary key on both of them.
    Thanks in advance.
    Attached Thumbnails Attached Thumbnails Constr1.jpg   Constr2.jpg  

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    Quote Originally Posted by amalia.stan View Post
    Code:
    UPDATE (select atrp.fid_parcela new_fid_parcela, sdfp.fid old_fid from tb_lm.atribute_parcele atrp, tb_lm.sdf_parcele sdfp where atrp.nr_cadastral=sdfp.nr_cadastral AND 
    atrp.nr_parcela=substr(sdfp.nr_parcela,0,1)) set new_fid_parcela=old_fid
    firstly, it is nice you use code tags for formatting the code, however, formatting is not only about using it. Also placing each section (SELECT, FROM, WHERE) to a new line, using reasonable line size and indenting the code makes it more readable and easier to understand. There are many tools which are doing this for you, e.g. this one: http://www.dpriver.com/pp/sqlformat.htm Please, take care about it; this UPDATE is quite simple, but this format makes me headaches when trying to understand it.
    Quote Originally Posted by amalia.stan View Post
    And I get the ora-01779 error.
    I atached printscreen with the unique constraind and also FID is primary key on both of them.
    Thanks in advance.
    To the question, the answer is the same as in your previous thread http://www.dbforums.com/oracle/16675...statement.html:
    a unique constraint (or primary key) is required to assure, that the "new" row will be updated with only one value.
    In this query, you are UPDATing ATRIBUTE_PARCELE table based on SDF_PARCELE rows and, as it is joined on (NR_CADASTRAL, SUBSTR(NR_PARCELA,0,1)), this tuple has to be unique in SDF_PARCELE. From the picture (again, could you post it in text, preferably in CREATE/ALTER TABLE statement?), only (NR_CADASTRAL, NR_PARCELA) is unique, which is insufficient.
    If there are two rows with (NR_CADASTRAL, NR_PARCELA) = ( 'A', 'AB' ), ( 'A', 'AC' ) in SDF_PARCELE (which do not violate SDF_PARCELE_UK1 constraint), from which of them shall be taken FID to UPDATE corresponding row(s) in ATRIBUTE_PARCELE table?

Posting Permissions

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