David P David P - 3 months ago 14
SQL Question

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?

Answer

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
  )
Comments