CODEWITHSUNDEEP
sql-updateoracle9i
PhelpsK
PhelpsK

Reputation: 217

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;

Upvotes: 16

Views: 106403

Answers (4)

user1471739
user1471739

Reputation: 1

I GOT THIS ERROR WHEN TRYING TO DO THE FOLLOWING :

UPDATE TABLE A SET (A.COLUMN1 , A.COLUMN2) = (SELECT VALUE1, VALUE2 FROM DUAL) WHERE A.KEY = :VARIABLE;

WHEN CHANGE TO :

UPDATE TABLE A SET A.COLUMN1 = VALUE1, A.COLUMN2 = VALUE2 WHERE A.KEY = :VARIABLE;

EVERY THING IS OK.

Upvotes: 0

Subhojyoti Ganguly
Subhojyoti Ganguly

Reputation: 11

Or we can use 

Where exists (select 1  from  TEMP_ECOLAB_PERSONS TE
                      where TE.PERSON=P.PERSON)

Upvotes: 1

Trevor North
Trevor North

Reputation: 2296

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;

Upvotes: 25

donny
donny

Reputation: 333

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.

Upvotes: 14

Related Questions