Search code examples
sqloraclesql-update

Update date difference from columns of same table

I have a table with 4 columns - FileName, CreatedDate,TodayDate, DifferenceDays.

Every day i run a perl script that adds new files that are created to the database (using INSERT). This part of script works fine.

For the old files that are already in the DB, i need to update the columns TodayDate with trunc(SYSDATE) and DifferenceDays with days difference between TodayDate and CreatedDate.

I tried the below query but it only updates the TodayDate but not the DifferenceDays:

UPDATE fileDB
SET TodayDate = trunc(SYSDATE),
    DifferenceDays = TodayDate - CreatedDate
WHERE trunc(TodayDate) != trunc(SYSDATE)

Please help me identify issue with my query.

Solution

  • When you do this:

    UPDATE fileDB
SET TodayDate = trunc(SYSDATE),
    DifferenceDays = TodayDate - CreatedDate
WHERE trunc(TodayDate) != trunc(SYSDATE)

    the calculation TodayDate - CreatedDate is using the current value of TodayDate, not the value it's being set to in this same statement.

    The simplest thing to do here is just repeat the trunc():

    UPDATE fileDB
SET TodayDate = trunc(SYSDATE),
    DifferenceDays = trunc(SYSDATE) - CreatedDate
WHERE trunc(TodayDate) != trunc(SYSDATE)

    In general, applying a function to a column in a filter, as you are with

    WHERE trunc(TodayDate) != trunc(SYSDATE)

    will prevent a plain index on that column being used. It's usually better to leave the value as it is if you can, e.g. assuming there are no dates in the future:

    WHERE TodayDate < trunc(SYSDATE)

    Here you seem to be updating nearly all your records, so an index wouldn't be used anyway.

    This seems like an odd way to maintain your data. It would be a lot less work (and less storage) to only have the CreatedDate column in the table, and then generate the other two columns as you query the table; possibly with a view defined to simplify that.