sql-updateoracle9i

Updating Multiple Columns from another table - Need Oracle format


I have a script that I use in SQL Server but I need to convert it to an Oracle format. Can anyone help?

    UPDATE PERSONS P SET 
        P.JOBTITLE=TE.JOBTITLE,
        P.LAST_NAME=TE.LAST_NAME,
        P.FIRST_NAME=TE.FIRST_NAME,
        P.DBLOGIN_ID=TE.DBLOGIN_ID,
        P.EMAIL_ID=TE.EMAIL_ID,
        P.USERLEVEL=TE.USERLEVEL,
        P.FACILITY_ID=TE.FACILITY_ID,
        P.SUPERVISOR=TE.SUPERVISOR,
        P.DEPARTMENT=TE.DEPARTMENT,
        P.WINLOGINID=TE.WINLOGINID
   FROM TEMP_ECOLAB_PERSONS TE
   WHERE P.PERSON=TE.PERSON;

--From the article below I came up with the following statement. It still doesn't work unfortunately:

  UPDATE (SELECT P.JOBTITLE, P.LAST_NAME, P.FIRST_NAME, P.DBLOGIN_ID, P.EMAIL_ID,
        P.USERLEVEL, P.FACILITY_ID, P.SUPERVISOR, P.DEPARTMENT,
        TE.JOBTITLE, TE.LAST_NAME, TE.FIRST_NAME, TE.DBLOGIN_ID, TE.EMAIL_ID,
        TE.USERLEVEL, TE.FACILITY_ID, TE.SUPERVISOR, TE.DEPARTMENT
     FROM PERSONS P, TEMP_ECOLAB_PERSONS TE WHERE P.PERSON=TE.PERSON)
  SET 
     P.JOBTITLE=TE.JOBTITLE,
     P.LAST_NAME=TE.LAST_NAME,
     P.FIRST_NAME=TE.FIRST_NAME,
     P.DBLOGIN_ID=TE.DBLOGIN_ID,
     P.EMAIL_ID=TE.EMAIL_ID,
     P.USERLEVEL=TE.USERLEVEL,
     P.FACILITY_ID=TE.FACILITY_ID,
     P.SUPERVISOR=TE.SUPERVISOR,
     P.DEPARTMENT=TE.DEPARTMENT; 

Solution

  • This is how I would do it. It might not be the best performance, but it works.

    MERGE INTO PERSONS_TMP PT
    USING ( 
        SELECT P.PERSON, P.JOB_TITLE, P.FIRST_NAME, P.LAST_NAME, P.FACILITY_ID 
        FROM PERSONS P) TMP
    ON (PT.PERSON = TMP.PERSON)
    WHEN MATCHED THEN 
    UPDATE SET 
        PT.FACILITY_ID = TMP.FACILITY_ID, 
        PT.JOB_TITLE = TMP.JOB_TITLE,
        PT.FIRST_NAME = TMP.FIRST_NAME,
        PT.LAST_NAME = TMP.LAST_NAME;
    

    The script above will update information in PERSONS_TMP table using data from PERSONS table. I believe in your case, you want it the other way around. So, please make sure you make the necessary changes before running the script.

    You can add "WHEN NOT MATCHED THEN.... " clause to the above SQL in case you need to insert new records, if it does not exist.