How can I Update a table changing only those rows which I want to change?-CodePudding

I have a table where the status should be changed based on the creation date. I tried to implement this using a DML request, but with such an update, changes occur on all rows. How can I make my code more efficient changing only those rows which which I indicated in CASE?

UPDATE your_day_test t
     SET t.status = CASE
                      WHEN t.create_date < trunc(SYSDATE - 28)
                           AND t.status = 1 THEN
                       -2
                      WHEN t.create_date < trunc(SYSDATE - 7)
                           AND t.status IN (0, 3) THEN
                       -1
                      WHEN t.create_date < trunc(SYSDATE - 42)
                           AND t.status = -2 THEN
                       -3
                      else
                         t.status
                    END
   WHERE trunc(t.create_date) > trunc(SYSDATE - 42)
   AND t.status IN (0, 1, 3, -2);
  COMMIT;

CodePudding user response：

You are checking the create_date within the range of 42 days with calendar date but filtering the create_date in where clause after today's date. So you just need to update your where condition like below -

UPDATE your_day_test t
   SET t.status = CASE WHEN t.create_date < trunc(SYSDATE - 7) AND t.status IN (0, 3) 
                            THEN -1
                       WHEN t.create_date < trunc(SYSDATE - 28) AND t.status = 1 
                            THEN -2
                       WHEN t.create_date < trunc(SYSDATE - 42) AND t.status = -2 
                            THEN -3
                       ELSE t.status
                  END
 WHERE t.create_date < trunc(SYSDATE - 42)
   AND t.status IN (0, 1, 3, -2);

CodePudding user response：

In Oracle you can update a result of select stamement so if you can write a select that selects only the rows that you want to update you can do:

update (
  select status, create_date
  from your_day_test
  WHERE trunc(t.create_date) > trunc(SYSDATE - 42)
    AND t.status IN (0, 1, 3, -2))
) set status = ...

CodePudding user response：

You can use the case statement in your where clause too. In that case you don't need the ELSE clause:

UPDATE your_day_test t
     SET t.status = CASE
                      WHEN t.create_date < trunc(SYSDATE - 28)
                           AND t.status = 1 THEN
                       -2
                      WHEN t.create_date < trunc(SYSDATE - 7)
                           AND t.status IN (0, 3) THEN
                       -1
                      WHEN t.create_date < trunc(SYSDATE - 42)
                           AND t.status = -2 THEN
                       -3
                    END
   WHERE CASE
                      WHEN t.create_date < trunc(SYSDATE - 28)
                           AND t.status = 1 THEN
                       -2
                      WHEN t.create_date < trunc(SYSDATE - 7)
                           AND t.status IN (0, 3) THEN
                       -1
                      WHEN t.create_date < trunc(SYSDATE - 42)
                           AND t.status = -2 THEN
                       -3
                      
                    END IN (-1,-2,-3);

CodePudding user response：

Add to your where condition an other predicate filtering the row where the update will have no effect

   WHERE trunc(t.create_date) > trunc(SYSDATE - 42)
   AND t.status IN (0, 1, 3, -2)
   /* filter for real updates */
   AND t.status != CASE
                      WHEN t.create_date < trunc(SYSDATE - 28)
                           AND t.status = 1 THEN
                       -2
                      WHEN t.create_date < trunc(SYSDATE - 7)
                           AND t.status IN (0, 3) THEN
                       -1
                      WHEN t.create_date < trunc(SYSDATE - 42)
                           AND t.status = -2 THEN
                       -3
                      else
                         t.status
                    END

Additionally if you get tired of updating the table each day (and you do not need index access on the changed status column) - you may produce the new status in a view only.

Example

create or replace  view my_status as
select
STATUS as STATUS_ORIG,
CREATE_DATE,
CASE
 WHEN create_date < trunc(SYSDATE - 28)
      AND status = 1 THEN
  -2
 WHEN create_date < trunc(SYSDATE - 7)
      AND status IN (0, 3) THEN
  -1  
 WHEN create_date < trunc(SYSDATE - 42)
      AND status = -2 THEN
  -3
 else
    status
END  as STATUS   
from tab a;