How to update particular column value to today's date for the particular ID and keeping other v-CodePudding

CREATE TABLE e_tab (
    e_id           NUMBER(10),
    eligible       VARCHAR2(30),
    assigned_date  TIMESTAMP
);

INSERT INTO e_tab VALUES(1,'Y',null);
INSERT INTO e_tab VALUES(2,'Y',null);
INSERT INTO e_tab VALUES(3,null,null);

SET SERVEROUTPUT ON;

DECLARE
    lv_flag NUMBER(10);
BEGIN
    SELECT
        COUNT(1)
    INTO lv_flag
    FROM
        e_tab
    WHERE
        assigned_date != sysdate;

    IF lv_flag < 1 THEN
        UPDATE e_tab
        SET
            assigned_date = current_timestamp
        WHERE
            eligible = 'Y';

    END IF;

    COMMIT;
END;

I have a table e_tab that I need to update based on the eligible column. If eligible column is Y then I need to update assigned_date column to current date and this will not happen frequently. Say for 2 E_ID that is 1 and 2 eligible column is Y so I have updated assigned_date column to today's date but for the third e_id i.e 3 that will be updated say after one day and will update eligible column to Y. Then, in this case, it should only update the third e_id to tomorrow's date and the previous one will be as it is.

Expected output:

 ------ ---------- ------------------------------- 
| E_ID | ELIGIBLE |         ASSIGNED_DATE         |
 ------ ---------- ------------------------------- 
|    1 | Y        | 02-02-22 3:53:46.449000000 PM |
|    2 | Y        | 02-02-22 3:53:46.449000000 PM |
|    3 | Y        | 03-02-22 3:53:46.449000000 AM |
 ------ ---------- -------------------------------

Will my code work for this scenario? And also if few more e_id got added later then same will happen for all the e_id

CodePudding user response：

You could create a trigger on e_tab so the assignment_date gets set whenever the eligible column value is set to 'Y'.

CREATE OR REPLACE TRIGGER e_tab_biu
    before insert or update 
    on e_tab
    for each row
begin
    IF :NEW.eligible = 'Y' AND NVL(:OLD.eligible,'N') != :NEW.eligible THEN
      :NEW.assigned_date := current_timestamp;
    END IF;
end e_tab_biu;
/

CodePudding user response：

You don't appear to need PL/SQL and can use a simple SQL statement:

UPDATE e_tab
SET eligible      = 'Y',
    assigned_date = CASE eligible
                    WHEN 'Y'
                    THEN current_date
                    ELSE current_date   INTERVAL '1' DAY
                    END
WHERE assigned_date IS NULL;

If you want to update all rows then remove the WHERE clause.

Will my code work for this scenario?

No, because your UPDATE statement will never update the third row as it filters on WHERE eligible = 'Y' and for that row eligible is NULL.

Update

The requirements are not well communicated but you appear, now, to be asking to update the rows where eligible is Y and where there is not a value for assigned_date. For this you can use:

UPDATE e_tab
SET assigned_date = current_date
WHERE assigned_date IS NULL
AND   eligible = 'Y';

At a later date (i.e. tomorrow), you would then have to change the third row to have eligible = 'Y' and then, if you want it to be a separate process, could run that update statement again.

db<>fiddle here