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

    Unanswered: Update statement

    hY,

    I'm tryed to update a table and this is my statement:

    Code:
    UPDATE ( SELECT 
                  AI.NR_CVARTAL new_nr_cvartal,
                  AI.NR_CF new_nr_cf,
                  AI.NR_TOPOGRAFIC new_nr_topografic,
                  AI.SUPRAFATA_ACTE new_suprafata_acte,
                  AI.TIP_ACT_PROPRIETATE new_tip_act_proprietate,
                  AI.ADRESA new_adresa,
                  AI.A new_A,
                  AI.C new_C,
                  AI.TERMO new_termo,
                  AI.E new_E,
                  AI.G new_G,
                  AI.T new_T,
                  AI.COD_ZONA_VALORICA new_cod_zona_valorica,
                  AI.COD_ZONA_PROTEJATA new_cod_zona_protejata,
                  AI.OBSERVATII new_observatii,
                  SDFI.NR_CVARTAL old_nr_cvartal,
                  SDFI.NR_CF old_nr_cf,
                  SDFI.NR_TOPOGRAFIC old_nr_topografic,
                  SDFI.SUPRAFATA_ACTE old_suprafata_acte,
                  SDFI.TIP_ACT_PROPRIETATE old_tip_act_proprietate,
                  SDFI.ADRESA old_adresa,
                  SDFI.A old_A,
                  SDFI.C old_C,
                  SDFI.TERMO old_termo,
                  SDFI.E old_E,
                  SDFI.G old_G,
                  SDFI.T old_T,
                  SDFI.COD_ZONA_VALORICA old_cod_zona_valorica,
                  SDFI.COD_ZONA_PROTEJATA old_cod_zona_protejata,
                  SDFI.OBSERVATII old_observatii from TB_LM.ALBA_IMOBIL AI, TB_LM.SDF_IMOBILE_IMPORTATE SDFI where AI.NR_CADASTRAL=SDFI.NR_CADASTRAL)
                  SET
                  new_nr_cvartal=old_nr_cvartal,
                  new_nr_cf=old_nr_cf,
                  new_nr_topografic=old_nr_topografic,
                  new_suprafata_acte=old_suprafata_acte,
                  new_tip_act_proprietate=old_tip_act_proprietate,
                  new_adresa=old_adresa, 
                  new_A=old_A, 
                  new_C=old_C,
                  new_termo=old_termo,
                  new_E=old_E,
                  new_G=old_G, 
                  new_T=old_T,
                  new_cod_zona_valorica=old_cod_zona_valorica, 
                  new_cod_zona_protejata=old_cod_zona_protejata,  
                  new_observatii=old_observatii;
    but i get this error :ORA-01779: cannot modify a column which maps to a non-key-preserved table
    Earlier I tryed with a smaller table to update one field and it worked.
    Thank in advance.

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

    the error has nothing to do with table size. It just says that a unique constraint (or primary key) is required to assure, that the "new" row will be updated with only one value.

    In your case, there should be at least unique constraint on column NR_CADASTRAL in table SDF_IMOBILE_IMPORTATE.

  3. #3
    Join Date
    Apr 2011
    Posts
    20
    it worked. very you very much

  4. #4
    Join Date
    Apr 2011
    Posts
    20
    Still I have a problem, it says that 152 rows were updated but the atributes are still empty. which may cause this?
    Field NR_CADASTRAL is string.
    Thanks.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Still I have a problem, it says that 152 rows were updated
    How many rows does ALBA_IMOBIL table contain?
    but the atributes are still empty. which may cause this?
    Here are some possible reasons:
    You are checking rows which have no matching row in SDF_IMOBILE_IMPORTATE
    There are NULLs in SDF_IMOBILE_IMPORTATE columns
    If you checked it from different session, UPDATE was not COMMITed before

  6. #6
    Join Date
    Apr 2011
    Posts
    20
    I found my error. I missplaced the tables. Correct is :
    Code:
    UPDATE ( SELECT 
                  SDFI.NR_CVARTAL old_nr_cvartal,
                  SDFI.NR_CF old_nr_cf,
                  SDFI.NR_TOPOGRAFIC old_nr_topografic,
                  SDFI.SUPRAFATA_ACTE old_suprafata_acte,
                  SDFI.TIP_ACT_PROPRIETATE old_tip_act_proprietate,
                  SDFI.ADRESA old_adresa,
                  SDFI.A old_A,
                  SDFI.C old_C,
                  SDFI.TERMO old_termo,
                  SDFI.E old_E,
                  SDFI.G old_G,
                  SDFI.T old_T,
                  SDFI.COD_ZONA_VALORICA old_cod_zona_valorica,
                  SDFI.COD_ZONA_PROTEJATA old_cod_zona_protejata,
                  SDFI.OBSERVATII old_observatii,
                  AI.NR_CVARTAL new_nr_cvartal,
                  AI.NR_CF new_nr_cf,
                  AI.NR_TOPOGRAFIC new_nr_topografic,
                  AI.SUPRAFATA_ACTE new_suprafata_acte,
                  AI.TIP_ACT_PROPRIETATE new_tip_act_proprietate,
                  AI.ADRESA new_adresa,
                  AI.A new_A,
                  AI.C new_C,
                  AI.TERMO new_termo,
                  AI.E new_E,
                  AI.G new_G,
                  AI.T new_T,
                  AI.COD_ZONA_VALORICA new_cod_zona_valorica,
                  AI.COD_ZONA_PROTEJATA new_cod_zona_protejata,
                  AI.OBSERVATII new_observatii FROM TB_LM.SDF_IMOBILE_IMPORTATE SDFI,TB_LM.ALBA_IMOBIL AI where SDFI.NR_CADASTRAL=AI.NR_CADASTRAL)
                  SET
                  old_nr_cvartal= new_nr_cvartal,
                  old_nr_cf=new_nr_cf,
                  old_nr_topografic=new_nr_topografic,
                  old_suprafata_acte=new_suprafata_acte,
                  old_tip_act_proprietate=new_tip_act_proprietate,
                  old_adresa=new_adresa, 
                  old_A=new_A, 
                  old_C=new_C,
                  old_termo=new_termo,
                  old_E=new_E,
                  old_G=new_G, 
                  old_T=new_T,
                  old_cod_zona_valorica= new_cod_zona_valorica, 
                  old_cod_zona_protejata= new_cod_zona_protejata,  
                  old_observatii= new_observatii;
    Thank you very much.

Posting Permissions

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