0

I have the below query regarding an updation basis on the column of EXIT_DATE date, now please advise.

I have the below query. Let's say today is 13 August, so I want to update all records starting on the basis of date 10 Aug , 11 Aug and 12 Aug considering the timestamp also to be very accurate.

In other words, I want to update all records of 3 days back.

Please advise is this query correct, or the below implementation could be improved.

UPDATE CTR_QWE SET STATUS = 'COMPLETED' 
WHERE STATUS IN ('N','P') 
  AND EXIT_DATE >= TRUNC(SYSDATE-3)
  AND EXIT_DATE <  TRUNC(SYSDATE);
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Possible duplicate of [How to get last 7 days data from current datetime to last 7 days in sql server](https://stackoverflow.com/questions/27599557/how-to-get-last-7-days-data-from-current-datetime-to-last-7-days-in-sql-server) – Mukesh A Aug 13 '18 at 07:12
  • This is a possible duplicate of [How to get last 7 days data from current datetime to last 7 days in sql server ] (https://stackoverflow.com/questions/27599557/how-to-get-last-7-days-data-from-current-datetime-to-last-7-days-in-sql-server) – Mukesh A Aug 13 '18 at 07:14
  • 1
    Query looks OK to me. If there's an index on EXIT_DATE, it'll be used (if optimizer chooses it). If it doesn't exist, create it. Whether it'll be an *improvement* or not, it is difficult to say - you should check the explain plan and see the differences. But, generally speaking, yes - it looks OK. – Littlefoot Aug 13 '18 at 07:19
  • The query looks good. Do you want to run this regularly? Then you don't need `EXIT_DATE >= TRUNC(SYSDATE-3)`. As Littlefoot mentioned, you may benefit from an index here. Try `create index idx1 on ctr_qwe(status, exit_date);` and `create index idx2 on ctr_qwe(exit_date, status);` and see whether one of them gets used. – Thorsten Kettner Aug 13 '18 at 07:33
  • Your query is good. – Gordon Linoff Aug 13 '18 at 10:47

1 Answers1

0

Code is good. I would only recommend to also add truncating your EXIT_DATE, since you're already truncating dates you're comparing it with.

It is also better to user BETWEEN AND in such circumstances when you need to write an interval for the one same date. That one makes no real difference in output, but is better for readability.

So:

UPDATE CTR_QWE
   SET STATUS = 'COMPLETED'
 WHERE STATUS IN ('N', 'P') 
   AND TRUNC(EXIT_DATE) BETWEEN TRUNC (SYSDATE - 3) AND TRUNC (SYSDATE - 1);
Goran Kutlaca
  • 2,014
  • 1
  • 12
  • 18
  • There is no improvement performance-wise. Your update statement will either be just as fast as the original or even slower. Yours can only then be faster when you provide a function index on `TRUNC(EXIT_DATE)`. – Thorsten Kettner Aug 13 '18 at 07:29
  • @ThorstenKettner You are right, when thinking of it, engine would have to translate `BETWEEN AND` the same as it would two times `AND` with `>=` and `<=`. So, there should be no improvement or difference. But, why do you think `BETWEEN AND` can be slower? – Goran Kutlaca Aug 13 '18 at 07:36
  • `TRUNC(EXIT_DATE)` can be slower, because it prevents a possible index on `EXIT_DATE` from being used. – Thorsten Kettner Aug 13 '18 at 07:48
  • @ThorstenKettner Aha, ok, you've been referencing to `TRUNC` when mentioning performance. Of course that `TRUNC` does not add to the performance (if nothing else, it's an additional operation to perform...), but I do consider it a good practice (when comparing dates with already truncated ones). So I wasn't referencing `TRUNC` when mentioning performance, it was stated in separated paragraph in which I wrote about 'BETWEEN ALL`. I also removed it from there 'cause your comment made me think for more then a sec, to realize that engine is translating it to the same code. Thank you for that. – Goran Kutlaca Aug 13 '18 at 08:19
  • I strongly discourage the use of `between` with date/time data types. It often has unexpected behavior when there is a time component. The OP's original version is preferable. – Gordon Linoff Aug 13 '18 at 10:46
  • @GordonLinoff Possible `unexpected behavior where there is a time component` is the very same reason I'm encouraging the use of TRUNC function - to remove that time component when it is obvious that OP wants to work only with dates. Is there any reason why `BETWEEN` should be discouraged with truncated dates, as I have used it? – Goran Kutlaca Aug 13 '18 at 11:24