Die einfache INNER JOIN Syntax wird auch mit Oracle 12 nicht unterstützt, daher diese Lösung über eine „Inline View“ gewählt:
UPDATE( SELECT emp.PHONE_NUMBER , DEPARTMENT_ID FROM EMPLOYEES emp INNER JOIN departments dept USING (DEPARTMENT_ID) WHERE dept.DEPARTMENT_NAME='IT' ) SET PHONE_NUMBER = '0890000'||to_char(DEPARTMENT_ID) /
Alternativ traditionell:
UPDATE EMPLOYEES emp SET PHONE_NUMBER = '0890000'|| ( SELECT to_char(dept.DEPARTMENT_ID) FROM departments dept WHERE dept.DEPARTMENT_NAME= 'IT' AND dept.DEPARTMENT_ID = emp.DEPARTMENT_ID ) WHERE emp.DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM departments dept WHERE dept.DEPARTMENT_NAME='IT') /
Es lassen sich auch mehrere Spalten auf einmal updaten:
UPDATE EMPLOYEES emp SET (PHONE_NUMBER , EMAIL) = ( SELECT '0890000'||to_char(dept.DEPARTMENT_ID),dept.DEPARTMENT_NAME||'@info.de' FROM departments dept WHERE dept.DEPARTMENT_NAME= 'IT' AND dept.DEPARTMENT_ID = emp.DEPARTMENT_ID ) WHERE emp.DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM departments dept WHERE dept.DEPARTMENT_NAME='IT') /
Wenn das zu langsam wird hift dann nur noch PL/SQL
DECLARE cursor c_old_id IS SELECT ed.LOCALID,per.per_id_1 FROM event_detail ed INNER JOIN ods.person per ON (per.per_id_3= ed.LOCALID); v_count pls_integer:=0; 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('Set '||rec.LOCALID||' to '||rec.per_id_1); v_count:=v_count+1; END loop; dbms_output.put_line('Update '||v_count||' Records'); END; /
An einfachsten und am besten lesbar ist die Verwendung des Merge Befehles:
MERGE INTO EMPLOYEES emp USING departments dept ON (dept.DEPARTMENT_ID = emp.DEPARTMENT_ID AND dept.DEPARTMENT_NAME= 'IT' ) WHEN MATCHED THEN UPDATE SET emp.PHONE_NUMBER = '0890000'||to_char(dept.DEPARTMENT_ID) /
Alternativ mit SQL Abfrage in den Using Clause:
MERGE INTO EMPLOYEES emp USING ( SELECT * FROM departments WHERE DEPARTMENT_NAME= 'IT') dept ON (dept.DEPARTMENT_ID = emp.DEPARTMENT_ID ) WHEN MATCHED THEN UPDATE SET emp.PHONE_NUMBER = '0890000'||to_char(dept.DEPARTMENT_ID) /
Einschränkung! ⇒ ORA-38104: Columns referenced in the ON Clause cannot be updated: