In the hypothetical scenario, I have a procedure with with an update statement which works by updating the values of specified table with the variable inputs. However, I have five AND clauses with an IN comparison function to validate if the variable values exists with the tables specified in the subquery inside the parenthesis. The procedure executes as expected but no value are updated. The value from the variables exist in the tables specified in the subqueries. Without these five AND clauses, the procedure updates the values as expected but I need to make these validations. Please let me know what could be the issue or if these is a better way to perform this validation. Please also see the image for reference on the specific code. Thank you in advance.
DECLARE VAR1 DATE := TO_DATE('11/15/2019', 'MM/DD/YYYY'); VAR2 VARCHAR2(25) := '0000809349'; VAR3 NUMBER := 2017; VAR4 NUMBER := 4; VAR5 DATE := TO_DATE('11/18/2019', 'MM/DD/YYYY'); BEGIN UPDATE MFT SET PD = VAR1, JI = VAR2, FY = VAR3, AP = VAR4, DPD = VAR5 WHERE RSN = 9097 AND VAR1 IN (SELECT PD FROM MEC) AND VAR2 IN (SELECT JI FROM CGL) AND VAR3 IN (SELECT FY FROM CGL) AND VAR4 IN (SELECT AP FROM CGL) AND VAR5 IN (SELECT DPD FROM CGL) AND PD = TO_DATE('06/09/2017', 'MM/DD/YYYY') AND JI = '0000911979' AND FY = 2017 AND AP = 12 AND DPD = TO_DATE('06/09/2017', 'MM/DD/YYYY'); COMMIT; END;