# Oracle -  Hilfe bei Updatestatement



## y0dA (24. August 2007)

Hi!
Folgendes Szenario:

In Tabelle A sollten folgende Felder geupdated werden: aa,bb,cc,dd,ee - apk ist primary key.

Die neuen Daten kommen aber aus der Tabelle B, welche in keinem direkten Verhältnis zu Tabelle A steht.

Nun gibt es eine View V, welche sowohl den Primärschlüssel von Tabelle A besitzt(vapk) als auch zwei weitere Felder (ff, gg), welche es ermöglichen die Werte von Tabelle B zu identifizieren.

Sprich man muss folgende Joins machen damit die korrekten Daten geupdadet werden:
A.apk = V.vapk, V.ff = B.ff

oder sehe ich das was falsch?

Das Problem ist nun wie ich dieses Update Stmt erstellen soll?
Update übere mehrere Tabellen geht in Oracle wohl nur über Subselect?


----------



## MPr (24. August 2007)

man könnte auch den Join updaten oder ein MERGE-Statement verwenden:

Update des Joins:

```
update (select a.col1 a_col, b.col2 b_col
          from tab1 a, tab2 b
         where a.joincol = b.joincol)
   set a_col = b_col;
```

Merge:

```
merge into tab1 a
using (select col2, joincol 
         from tab2) b
on (a.joincol = b.joincol)
when matched then update set a.col1 = b.col2
```

Wenn man sich an die MERGE-Syntax gewöhnt hat, wird diese Variante ziemlich intuitiv. Bei Tom Kyte findet man jede Menge Beispiele dazu: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:164612348068 (genau wie zu jeder anderen Oracle-Frage)

Gruß

MP


----------



## y0dA (24. August 2007)

Hi!
Zunächst danke für deine Antwort, jedoch kann ich das ja nicht so umsetzen, weil es ja eigentlich 3 Tabellen sind (und eine soll aktualisiert werden).

Hier mal mein momentanes, grottenschlechtes, nicht funktionierendes statement:


```
UPDATE e_30019_save e
  SET e.geo_merc_x = 
      ( SELECT round(f_dms2mercatorx(TO_NUMBER(substr(w.GPS_E,1,2)),TO_NUMBER(substr(w.GPS_E,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_E,6,4))) ) ,0) NEU_MERC_X
               FROM waizinger w, v_standorte_geo v
               WHERE
                    (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler) 
                                        OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler)
                    )
                    AND v.SAMMLER_NR = 3
                    AND v.gemnr  = w.gemnr
                    AND e.dbk = v.E_30019_DBK
                    AND w.GPS_H is not null
      ),
      e.geo_merc_Y = 
        ( SELECT round(f_dms2mercatory(TO_NUMBER(substr(w.GPS_N,1,2)),TO_NUMBER(substr(w.GPS_N,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_N,6,4))) ) ,0) NEU_MERC_Y
               FROM waizinger w, v_standorte_geo v
               WHERE
                    (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler) 
                                        OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler)
                    )
                    AND v.SAMMLER_NR = 3
                    AND v.gemnr  = w.gemnr
                    AND e.dbk = v.E_30019_DBK
                    AND w.GPS_H is not null
      ),
      e.geo_qual = 7,
      e.geo_naehe = 0,
      e.geo_upd_i_90023_id = 14,
      e.geo_upd =
        ( SELECT SYSDATE
                 FROM dual
        )
```

Also die Tabellen sind wie folgt verknüpft:
e30019.dbk=v_Standorte_geo.e30019_dbk
v_standorte_geo = waizinger.gemnr + waizinger.sammler oder v_Standorte_geo.= waizinger.sammler


----------



## MPr (24. August 2007)

hier mal die MERGE-Variante - natürlich ohne Gewähr ...


```
merge into e_30019_save e
using (SELECT v.E_30019_DBK,
              round(f_dms2mercatorx(TO_NUMBER(substr(w.GPS_E,1,2)), TO_NUMBER(substr(w.GPS_E,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_E,6,4))) ) ,0) NEU_MERC_X,
              round(f_dms2mercatory(TO_NUMBER(substr(w.GPS_N,1,2)),TO_NUMBER(substr(w.GPS_N,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_N,6,4))) ) ,0) NEU_MERC_Y
         FROM waizinger w, v_standorte_geo v
        WHERE (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler) 
                OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler))
          AND v.SAMMLER_NR = 3
          AND v.gemnr  = w.gemnr
          AND w.GPS_H is not null) r
on (e.dbk = v.E_30019_DBK)          
when matched then update set e.geo_merc_x = r.NEU_MERC_X, 
                             e.geo_merc_Y = NEU_MERC_Y,
                             e.geo_qual = 7,
                             e.geo_naehe = 0,
                             e.geo_upd_i_90023_id = 14,
                             e.geo_upd = sysdate;
```

Da die WHERE-Klauseln der Subqueries, so weit ich sehen konnte, identisch sind, sollte sich der Zugriff zusammenfassen lassen.

Gruß

MP


----------



## y0dA (24. August 2007)

Danke für das Statement, nehme an dass es auch funktionieren würde, nur leider ist die besagte Oracle eine alte Dame (wohl 8er Version) - kann auch kein "INNER JOIN"



**EDIT**
Hier ist das Ausgangsstatement für die where sachen (stimmt oben wohl nicht ganz):
*and v.SAMMLER_NR = 3 and v.gemnr  = Waizinger.gemnr)* gehört zu den trim sachen


```
select * from Waizinger where exists
(select 'x' from V_STANDORTE_GEO v where 
      (trim(v.STO_NR_SAM) = trim(Waizinger.gemnr)||'/'||trim(Waizinger.Sammler) 
      or trim(v.STO_NR_SAM) = trim(Waizinger.Sammler)
      )
      and v.SAMMLER_NR = 3 and v.gemnr  = Waizinger.gemnr)
and Waizinger.GPS_H is not null
order by Waizinger.gemnr,Waizinger.Sammler
```


----------



## MPr (24. August 2007)

MERGE ist meiner Erinnerung eine 9er Errungenschaft. Seit wann man Joins updaten kann, weiß ich nicht mehr (in 8i sollte es schon funktioniert haben), aber hier wäre noch diese Variante:


```
update (SELECT e.geo_merc_x,
               e.geo_merc_Y,
               e.geo_qual,
               e.geo_naehe,
               e.geo_upd_i_90023_id,
               e.geo_upd,               
               round(f_dms2mercatorx(TO_NUMBER(substr(w.GPS_E,1,2)), TO_NUMBER(substr(w.GPS_E,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_E,6,4))) ) ,0) NEU_MERC_X,
               round(f_dms2mercatory(TO_NUMBER(substr(w.GPS_N,1,2)),TO_NUMBER(substr(w.GPS_N,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_N,6,4))) ) ,0) NEU_MERC_Y
          FROM waizinger w, v_standorte_geo v, e_30019_save e
         WHERE (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler) 
                 OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler))
           AND v.SAMMLER_NR = 3
           AND v.gemnr  = w.gemnr
           AND w.GPS_H is not null  
           AND e.dbk = v.E_30019_DBK) t
	set t.geo_merc_x = t.NEU_MERC_X,       
	    t.geo_merc_Y = t.NEU_MERC_Y,         
	    t.geo_qual = 7,                    
	    t.geo_naehe = 0,                   
	    t.geo_upd_i_90023_id = 14,         
	    t.geo_upd = sysdate;
```

Gruß

MP


----------



## y0dA (24. August 2007)

Echt verdammt nett, dass du dir hier soviel Mühe mit mir gibst 

Leider funktioniert das Statement nicht, bekomme folgende Fehlermeldung:

```
ORA-01779 kann keine Spalte, die einer Basistabelle zugeordnet wird, verändern
```

Heißt dass, das dieses Update Statement, in diesesr Konstelllation, auch nicht unterstützt wird?

Mittlerweile bin ich am verzeifeln


----------



## MPr (24. August 2007)

ORA-01779: 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.

Da fehlt dem System ein Constraint, der sicherstellt, dass es keine Mehrfachzuordnungen geben kann. Tom Kyte sagt dazu:



> Here we update a join.  We can only modify the columns in one of the tables and the
> other tables we are *NOT* modifying must be "key preserved" -- that is, we must be able
> to verify that at most one record will be returned when we join NAME to this other table.
> In order to do that, keyname in LOOKUP must either be a primary key or have a unique
> ...



Unter http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:273215737113 gibt's dazu noch ausführlichere Erläuterungen. Wenn kein Constraint definiert werden kann, muss man wohl doch ein einfaches UPDATE verwenden. Dabei könnte man dann allerdings die beiden konkatenierten Werte in einer Subquery unterbringen, also:


```
SQL> update test set (a, b) = (select 1, 2 from dual);

1 Zeile wurde aktualisiert.
```

Gruß

MP


----------



## y0dA (25. August 2007)

Da die DB nicht mir gehört, weiß ich nicht ob und wie ich diesen Constraint setzen soll. Also muss ich doch so ein Stmt benutzen wie ich oben schon gepostet habe?



```
SQL> UPDATE test SET (a, b) = (SELECT 1, 2 FROM dual); 1 Zeile wurde aktualisiert.
```

MIt dem hier weiß ich leider überhaupt nichts anzufangen (echt beschämend :/ ).


----------



## MPr (25. August 2007)

gemeint war nur, dass man sich auf eine korrelierte Subquery für den SET-Teil beschränken kann, wenn die Queries von der Bedingung her identisch sind. Was in der Ursprungsversion noch fehlt, ist eine WHERE-Bedingung, denn es sollen ja wahrscheinlich nicht alle Sätze der Tabelle aktualisiert werden, sondern nur die, für die in den Lookup-Tabellen eine Entsprechung existiert. Das Statement würde dann ungefähr folgendermaßen aussehen:


```
UPDATE e_30019_save e
  SET (e.geo_merc_x, e.geo_merc_Y) = 
        ( SELECT round(f_dms2mercatorx(TO_NUMBER(substr(w.GPS_E,1,2)),TO_NUMBER(substr(w.GPS_E,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_E,6,4))) ) ,0) NEU_MERC_X,
                 round(f_dms2mercatory(TO_NUMBER(substr(w.GPS_N,1,2)),TO_NUMBER(substr(w.GPS_N,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_N,6,4))) ) ,0) NEU_MERC_Y
               FROM waizinger w, v_standorte_geo v
               WHERE
                    (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler) 
                                        OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler)
                    )
                    AND v.SAMMLER_NR = 3
                    AND v.gemnr  = w.gemnr
                    AND e.dbk = v.E_30019_DBK
                    AND w.GPS_H is not null
      ),
      e.geo_qual = 7,
      e.geo_naehe = 0,
      e.geo_upd_i_90023_id = 14,
      e.geo_upd = SYSDATE
where exists ( SELECT NULL
               FROM waizinger w, v_standorte_geo v
               WHERE
                    (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler) 
                                        OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler)
                    )
                    AND v.SAMMLER_NR = 3
                    AND v.gemnr  = w.gemnr
                    AND e.dbk = v.E_30019_DBK
                    AND w.GPS_H is not null
      )
```

Gruß

MP


----------



## y0dA (27. August 2007)

Nun bekomme ich folgende Meldung (bekam ich auch schon bei dem von mir geposteten Stmt!):

```
ORA-01427 Unterabfrage für eine Zeile liefert mehr als eine Zeile
```

Hier nochmal das Stmt mit welchem ich auf die Where-Klausel komme:


```
select * from Waizinger where not exists
(select 'x' from V_STANDORTE_GEO v where 
      (trim(v.STO_NR_SAM) = trim(Waizinger.gemnr)||'/'||trim(Waizinger.Sammler) 
      or trim(v.STO_NR_SAM) = trim(Waizinger.Sammler)
      )
      and v.SAMMLER_NR = 3 and v.gemnr  = Waizinger.gemnr)
and Waizinger.GPS_H is null
order by Waizinger.gemnr,Waizinger.Sammler
```

Hierbei bekomme ich ungefähr ~2400 Zeilen raus und eben jene möchte ich mit meinem Update-Stmt updaten.


----------



## MPr (27. August 2007)

offenbar liefert die Unterabfrage mehrere Ergebnisse, so dass die DB nicht entscheiden kann, welches davon verwendet werden soll. Man könnte mal versuchen, das Ergebnis der Subquery auf Eindeutigkeit zu prüfen, also etwa:


```
select E_30019_DBK, NEU_MERC_X, NEU_MERC_Y, count(*)
  from (SELECT v.E_30019_DBK,
				       round(f_dms2mercatorx(TO_NUMBER(substr(w.GPS_E,1,2)),TO_NUMBER(substr(w.GPS_E,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_E,6,4))) ) ,0) NEU_MERC_X,
				       round(f_dms2mercatory(TO_NUMBER(substr(w.GPS_N,1,2)),TO_NUMBER(substr(w.GPS_N,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_N,6,4))) ) ,0) NEU_MERC_Y
				     FROM waizinger w, v_standorte_geo v
				     WHERE
				          (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler)
				                              OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler)
				          )
				          AND v.SAMMLER_NR = 3
				          AND v.gemnr  = w.gemnr
				          AND v.E_30019_DBK in (select dbk
				                                  from e_30019_save)
				          AND w.GPS_H IS NOT NULL)
 group by E_30019_DBK, NEU_MERC_X, NEU_MERC_Y
having count(*) > 1;
```

Allerdings muss ich gestehen, dass mich das letzte Stücke Code (diesmal mit NOT EXISTS) etwas verwirrt - bisher ging ich davon aus, dass es um eine EXISTS-Prüfung ginge. Sollte der Aktualisierungs-Prozess tatsächlich eher etwas unübersichtlich sein, wär's vielleicht eine Überlegung wert, ob man ihn nicht doch eher in Einzelschritte auflösen möchte (z.B. unter Verwendung temporärer Tabellen). Das entspricht zwar nicht der reinen Lehre (und könnte temporäre Sperren erforderlich machen, um die Konsistenz der Änderungen zu gewährleisten), hätte aber den Vorteil, dass man eher im Blick behalten könnte, ob die DML-Operation auch das tut, was man von ihr möchte.

Gruß

MP


----------



## y0dA (27. August 2007)

hi!
Hatte mich falsch ausgedrückt!
Folgendes beschreibt jene die vom Update ausgeschlossen werden sollen:

```
select * from Waizinger where not exists
(select 'x' from V_STANDORTE_GEO v where 
      (trim(v.STO_NR_SAM) = trim(Waizinger.gemnr)||'/'||trim(Waizinger.Sammler) 
      or trim(v.STO_NR_SAM) = trim(Waizinger.Sammler)
      )
      and v.SAMMLER_NR = 3 and v.gemnr  = Waizinger.gemnr)
and Waizinger.GPS_H is null
order by Waizinger.gemnr,Waizinger.Sammler
```

Demnach sollten jene Upgedadet werden:

```
select * from Waizinger where exists
(select 'x' from V_STANDORTE_GEO v where 
      (trim(v.STO_NR_SAM) = trim(Waizinger.gemnr)||'/'||trim(Waizinger.Sammler) 
      or trim(v.STO_NR_SAM) = trim(Waizinger.Sammler)
      )
      and v.SAMMLER_NR = 3 and v.gemnr  = Waizinger.gemnr)
and Waizinger.GPS_H is not null
order by Waizinger.gemnr,Waizinger.Sammler
```

Das ganze ist wirklich nicht sehr übersichtlich, zumal die benutzte View auf etliche Tabellen zugreift (und hiermit ist das ganze auch sehr sehr langsam) und zweitens die tabelle waizinger daten beinhaltet, welche von einem excel file eingespielt wurden (wurde nicht von mir eingegeben u ist nicht immer "regelkonform" - deshalb auch die TRIMs und concats).
Werde nun das Select mal ausführen und mal schauen was dabei rauskommt.

vielen dank derweil.

**EDIT**
So nun hab ich es endlich!
Die View liefert tatsächlich bei 5 Sätzen doppelte Werte, dies konnte ich nun mit einem 
	
	
	



```
DISTINCT
```
 ausräumen:


```
UPDATE e_30019_save e
  SET (e.geo_merc_x, e.geo_merc_Y) = 
        ( SELECT DISTINCT round(f_dms2mercatorx(TO_NUMBER(substr(w.GPS_E,1,2)),TO_NUMBER(substr(w.GPS_E,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_E,6,4))) ) ,0) NEU_MERC_X,
                 round(f_dms2mercatory(TO_NUMBER(substr(w.GPS_N,1,2)),TO_NUMBER(substr(w.GPS_N,4,2)),60 * TO_NUMBER('0' || TO_CHAR(substr(w.GPS_N,6,4))) ) ,0) NEU_MERC_Y
               FROM waizinger w, v_standorte_geo v
               WHERE
                    (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler) 
                                        OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler)
                    AND v.SAMMLER_NR = 3
                    AND v.gemnr  = w.gemnr
                    )
                    AND e.dbk = v.E_30019_DBK
                    AND w.GPS_H IS NOT NULL
      ),
      e.geo_qual = 7,
      e.geo_naehe = 0,
      e.geo_upd_i_90023_id = 14,
      e.geo_upd = SYSDATE
WHERE EXISTS ( SELECT NULL
               FROM waizinger w, v_standorte_geo v
               WHERE
                    (TRIM(v.STO_NR_SAM) = TRIM(w.gemnr)||'/'||TRIM(w.Sammler) 
                                        OR TRIM(v.STO_NR_SAM) = TRIM(w.Sammler)
                    AND v.SAMMLER_NR = 3
                    AND v.gemnr  = w.gemnr
                    )
                    AND e.dbk = v.E_30019_DBK
                    AND w.GPS_H IS NOT NULL
      )
```

Nochmals vielen Dank für deine Hilfe, ich fühle mich nun richtig gestärkt im Thema Update  (habe einiges gelernt, kannte nur 0815 Update Stmts bisher).


----------

