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