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;
UPDATE PERSONS P
SET (jobtitle,
last_name,
first_name,
dblogin_id,
email_Id,
userlevel,
facility_id,
supervisor,
department,
winloginid) = (select jobtitle,
last_name,
first_name,
dblogin_id,
email_Id,
userlevel,
facility_id,
supervisor,
department,
winloginid
from TEMP_ECOLAB_PERSONS TE
where TE.PERSON=P.PERSON);
Note that if there are other rows present in persons that are not in temp_ecolab_persons, these extra rows in the person table will be set to null (or could cause the statement to fail with not null constraint error by the update above so if this is the case, you may also need a where clause on the update statement to restrict these, e.g. if i know the email_id field is populated on some records but not on others, i can limit the update to just those rows as follows
UPDATE PERSONS P
SET (jobtitle,
last_name,
first_name,
dblogin_id,
email_Id,
userlevel,
facility_id,
supervisor,
department,
winloginid) = (select jobtitle,
last_name,
first_name,
dblogin_id,
email_Id,
userlevel,
facility_id,
supervisor,
department,
winloginid
from TEMP_ECOLAB_PERSONS TE
where TE.PERSON=P.PERSON)
WHERE email_id is null;