CODEWITHSUNDEEP
sqloracle-database
David P
David P

Reputation: 2093

Correlated Update With Conditions

I am trying to update the values of one table with the updated values from a second table. However, since I am updating the MODIFIED_BY and MODIFIED_ON columns of the first table, I only want to update the rows where there is an updated value:

UPDATE SAFETY_ADMIN.SAFETY_USERS tbl1
  SET (SUPERVISOR_ID,DEPT_ID,USER_ID,EMPLOYEE_TYPE,EMPLOYEE_NAME,EMAIL,MODIFIED_BY,MODIFIED_ON) = 
(
  SELECT SAFETY_ADMIN.FN_GETSUPERVISORID(tbl2.SUPID),CAST(tbl2.DEPTID AS NUMBER(19)),UPPER(SUBSTR(tbl2.EMAIL,1,INSTR(tbl2.EMAIL,'@')-1)),tbl2.EMPTYPE,tbl2.EMPNAME,tbl2.EMAIL,SYS_CONTEXT('USERENV', 'OS_USER'),CURRENT_TIMESTAMP
  FROM PS_LOAD.EMPLOYEEDATA tbl2 
  WHERE tbl1.EMPLOYEE_ID = CAST(tbl2.EMPID AS NUMBER(19)) AND 
  (
    tbl1.SUPERVISOR_ID <> SAFETY_ADMIN.FN_GETSUPERVISORID(tbl2.SUPID) OR
    tbl1.DEPT_ID <> CAST(tbl2.DEPTID AS NUMBER(19)) OR
    tbl1.USER_ID <> UPPER(SUBSTR(tbl2.EMAIL,1,INSTR(tbl2.EMAIL,'@')-1)) OR
    tbl1.EMPLOYEE_TYPE <> tbl2.EMPTYPE OR
    tbl1.EMPLOYEE_NAME <> tbl2.EMPNAME OR
    tbl1.EMAIL <> tbl2.EMAIL
  )
);

However, my query complains about updating DEPT_ID with a NULL value, even though there are no null values present. I feel I am placing the WHERE conditions in the wrong place. I am more of a SQL Server guy. Can somebody tell me how to do this in Oracle?

Upvotes: 0

Views: 43

Answers (2)

user330315
user330315

Reputation:

In Oracle updates that need to get data from other tables are typically written using a MERGE, which is much shorter as a plain UPDATE with a co-related sub-query

MERGE INTO safety_admin.safety_users su
USING (
  SELECT safety_admin.fn_getsupervisorid(ed.supid) as supervisor_id,
         to_number(ed.deptid) as deptid,
         to_number(ed.empid) as empid,
         upper(substr(ed.email,1,instr(ed.email,'@')-1)) as user_id,
         ed.emptype,
         ed.empname,
         ed.email,
         ed.empid
  FROM ps_load.employeedata ed 
) t ON (su.employee_id = t.empid)
WHEN MATCHED THEN UPDATE
  SET (supervisor_id, dept_id, user_id, employee_type, employee_name, email, modified_by, modified_on) 
    = (t.supervisor_id, t.deptid, t.email, t.emptype, t.empname, sys_context('userenv', 'os_user'), current_timestamp)
WHERE ( su.supervisor_id <> t.supervisor_id OR
        su.dept_id <> t.deptid OR
        su.user_id <> user_id OR
        su.employee_type <> t.emptype OR
        su.employee_name <> t.empname OR
        su.email <> t.email);

I am not entirely sure set (a,b,c,) = (....) is valid in SET part of a MERGE. If that doesn't work, you'll need to specify each column individually.

Converting a varchar to a number can be done a bit shorter using to_number() instead of the somewhat longer cast() operator.

There is also no need to write everything in uppercase in Oracle, SQL is not case sensitive.

Upvotes: 1

PKey
PKey

Reputation: 3841

I think that if you add where exists clause, it should work for you...

UPDATE SAFETY_ADMIN.SAFETY_USERS tbl1
  SET (SUPERVISOR_ID,DEPT_ID,USER_ID,EMPLOYEE_TYPE,EMPLOYEE_NAME,EMAIL,MODIFIED_BY,MODIFIED_ON) = 
(
  SELECT SAFETY_ADMIN.FN_GETSUPERVISORID(tbl2.SUPID),CAST(tbl2.DEPTID AS NUMBER(19)),UPPER(SUBSTR(tbl2.EMAIL,1,INSTR(tbl2.EMAIL,'@')-1)),tbl2.EMPTYPE,tbl2.EMPNAME,tbl2.EMAIL,SYS_CONTEXT('USERENV', 'OS_USER'),CURRENT_TIMESTAMP
  FROM PS_LOAD.EMPLOYEEDATA tbl2 
  WHERE tbl1.EMPLOYEE_ID = CAST(tbl2.EMPID AS NUMBER(19)) AND 
  (
    tbl1.SUPERVISOR_ID <> SAFETY_ADMIN.FN_GETSUPERVISORID(tbl2.SUPID) OR
    tbl1.DEPT_ID <> CAST(tbl2.DEPTID AS NUMBER(19)) OR
    tbl1.USER_ID <> UPPER(SUBSTR(tbl2.EMAIL,1,INSTR(tbl2.EMAIL,'@')-1)) OR
    tbl1.EMPLOYEE_TYPE <> tbl2.EMPTYPE OR
    tbl1.EMPLOYEE_NAME <> tbl2.EMPNAME OR
    tbl1.EMAIL <> tbl2.EMAIL
  )
) where exists 
(
  SELECT 1 
  FROM PS_LOAD.EMPLOYEEDATA tbl3 
  WHERE tbl1.EMPLOYEE_ID = CAST(tbl3.EMPID AS NUMBER(19)) AND 
  (
    tbl1.SUPERVISOR_ID <> SAFETY_ADMIN.FN_GETSUPERVISORID(tbl3.SUPID) OR
    tbl1.DEPT_ID <> CAST(tbl3.DEPTID AS NUMBER(19)) OR
    tbl1.USER_ID <> UPPER(SUBSTR(tbl3.EMAIL,1,INSTR(tbl3.EMAIL,'@')-1)) OR
    tbl1.EMPLOYEE_TYPE <> tbl3.EMPTYPE OR
    tbl1.EMPLOYEE_NAME <> tbl3.EMPNAME OR
    tbl1.EMAIL <> tbl3.EMAIL
  )

Upvotes: 1

Related Questions