prog:sql_update_syntax
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:sql_update_syntax [2016/10/06 17:28] – [Merge Lösung] gpipperr | prog:sql_update_syntax [2016/10/06 17:40] (aktuell) – [Inline View für die SQL 92 Syntax] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ===== Oracle SQL - Update und Merge mit Daten in einer Tabelle mit Werten aus einer zweiten Tabelle ===== | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | **Aufgabe: | ||
+ | |||
+ | {{: | ||
+ | |||
+ | |||
+ | ==== Update Lösung ==== | ||
+ | |||
+ | === Inline View für die SQL 92 Syntax === | ||
+ | |||
+ | Die einfache INNER JOIN Syntax wird auch mit Oracle 12 nicht unterstützt, | ||
+ | <code SQL> | ||
+ | UPDATE( | ||
+ | select emp.PHONE_NUMBER | ||
+ | , DEPARTMENT_ID | ||
+ | from EMPLOYEES emp inner join departments dept using (DEPARTMENT_ID) | ||
+ | where dept.DEPARTMENT_NAME=' | ||
+ | ) | ||
+ | SET PHONE_NUMBER = ' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | Alternativ traditionell: | ||
+ | <code SQL> | ||
+ | UPDATE EMPLOYEES emp | ||
+ | set PHONE_NUMBER = ' | ||
+ | from departments dept | ||
+ | | ||
+ | and dept.DEPARTMENT_ID | ||
+ | where emp.DEPARTMENT_ID in ( select DEPARTMENT_ID from departments dept where dept.DEPARTMENT_NAME=' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | Es lassen sich auch mehrere Spalten auf einmal updaten: | ||
+ | |||
+ | <code SQL> | ||
+ | UPDATE EMPLOYEES emp | ||
+ | set (PHONE_NUMBER , EMAIL) | ||
+ | from departments dept | ||
+ | | ||
+ | and dept.DEPARTMENT_ID | ||
+ | where emp.DEPARTMENT_ID in ( select DEPARTMENT_ID from departments dept where dept.DEPARTMENT_NAME=' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | Wenn das zu langsam wird hift dann nur noch PL/SQL | ||
+ | <code sql> | ||
+ | declare | ||
+ | cursor c_old_id | ||
+ | select ed.LOCALID, | ||
+ | from event_detail ed | ||
+ | inner join ods.person per on (per.per_id_3= ed.LOCALID); | ||
+ | v_count pls_integer: | ||
+ | begin | ||
+ | for rec in c_old_id | ||
+ | loop | ||
+ | update event_detail set LOCALID=rec.per_id_1 where LOCALID=rec.LOCALID; | ||
+ | dbms_output.put_line(' | ||
+ | v_count: | ||
+ | end loop; | ||
+ | dbms_output.put_line(' | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | ---- | ||
+ | |||
+ | ==== Merge Lösung ==== | ||
+ | |||
+ | An einfachsten und am besten lesbar ist die Verwendung des Merge Befehles: | ||
+ | <code sql> | ||
+ | MERGE INTO EMPLOYEES emp | ||
+ | USING departments dept | ||
+ | ON (dept.DEPARTMENT_ID | ||
+ | WHEN MATCHED THEN | ||
+ | UPDATE SET emp.PHONE_NUMBER = ' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | Alternativ mit SQL Abfrage in den Using Clause: | ||
+ | <code sql> | ||
+ | MERGE INTO EMPLOYEES emp | ||
+ | USING ( select * from departments where DEPARTMENT_NAME= ' | ||
+ | ON (dept.DEPARTMENT_ID | ||
+ | WHEN MATCHED THEN | ||
+ | UPDATE SET emp.PHONE_NUMBER = ' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | <fc # | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | Update: | ||
+ | * https:// | ||
+ | |||
+ | Merge: | ||
+ | * https:// |
prog/sql_update_syntax.txt · Zuletzt geändert: 2016/10/06 17:40 von gpipperr