UPDATE Statement doesn't update any row

Question

I got two tables (ORACLE): D_CONTROL_CARGA and F_PS_CARGA_DIARIA_D

SELECT control_id, control_cd, control_query_tx 
FROM ONHR_DIM.D_CONTROL_CARGA;

table D_CONTROL_CARGA

SELECT FECHA_ID, CONTROL_ID, CONTROL_CD, CANTIDAD_CA 
FROM onhr_dim.F_PS_CARGA_DIARIA_D;

table F_PS_CARGA_DIARIA_D

I want to update the column CANTIDAD_CA with this PL/SQL code:

DECLARE
sql_qry ONHR_DIM.D_CONTROL_CARGA.control_query_tx%TYPE;
cantidad_ca ONHR_DIM.F_PS_CARGA_DIARIA_D.CANTIDAD_CA%TYPE;
CURSOR cQueries IS SELECT control_id,control_query_tx FROM ONHR_DIM.D_CONTROL_CARGA;
BEGIN
   FOR reg IN cQueries LOOP
        sql_qry:=SUBSTR(reg.control_query_tx, 0, LENGTH(reg.control_query_tx) - 1);
        IF sql_qry not like '%to_char(sysdate%' THEN 
           execute immediate sql_qry into cantidad_ca;
           UPDATE ONHR_DIM.F_PS_CARGA_DIARIA_D SET CANTIDAD_CA=cantidad_ca WHERE CONTROL_ID=reg.control_id AND FECHA_ID=TRUNC(SYSDATE);        
        END IF;
    END LOOP;
END;

Then I got: Updated Rows -1

What Do I have to change?

Your code doesn't have any output, so where are you seeing "Updated Rows -1"? If your update is executed then it doesn't do anything - `CANTIDAD_CA=cantidad_ca` is setting the column value to its existing value. Give your PL/SQL variable a different name to the table column. It isn't clear if that is what you are asking about though. `FECHA_ID=TRUNC(SYSDATE)` looks dodgy too; if `fecha_id` is a date then OK (ish), but if it's a string then specify the format. (Also, please post your tables as text, not images.) — Alex Poole, Apr 17 '21 at 16:29
One reason (maybe there are more) is that the strings in your table are all upper-case, but in the `IF` statement you compare to a lower-case string. Obviously those will never match. — , Apr 17 '21 at 16:29

UPDATE Statement doesn't update any row

0 Answers0