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:

SQL:

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:

SQL:

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:

Code:

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 ...

SQL:

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

Code:

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:

SQL:

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:

Code:

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
constraint applied to it...

scott@ORA734.WORLD> update
2 ( select columnName, value
3 from name, lookup
4 where name.keyname = lookup.keyname
5 and lookup.otherColumn = ther_value )
6 set columnName = value
7 /

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:

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?

Code:

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:

SQL:

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

Oracle - Hilfe bei Updatestatement

y0dA

Erfahrenes Mitglied

MPr

Erfahrenes Mitglied

y0dA

Erfahrenes Mitglied

MPr

Erfahrenes Mitglied

y0dA

Erfahrenes Mitglied

MPr

Erfahrenes Mitglied

y0dA

Erfahrenes Mitglied

MPr

Erfahrenes Mitglied

y0dA

Erfahrenes Mitglied

MPr

Erfahrenes Mitglied

Neue Beiträge