MySQL/Oracle – Easy Query to Fix Historical Columns valid_from and valid_to

MySQLoracle

I have a table with typical history rows using valid_from and valid_to dates. My table might look like this:

| id | object_id | name | valid_from | valid_to
--------------------------------------------------
|  1 | 1000      | Foo  | 2010-07-01 |   null
|  2 | 1000      | Bar  | 2011-12-31 |   null
|  3 | 2000      | Baz  | 2010-08-04 |   null
|  4 | 2000      | Bang | 2013-03-21 |   null

I'd like run a SQL update such that it would update valid_to of a row to next historical valid_from date minus 1 day if possible. So therefore the table might look like this when the query is done:

| id | object_id | name | valid_from | valid_to
--------------------------------------------------
|  1 | 1000      | Foo  | 2010-07-01 |   2011-12-30
|  2 | 1000      | Bar  | 2011-12-31 |   null
|  3 | 2000      | Baz  | 2010-08-04 |   2013-03-20
|  4 | 2000      | Bang | 2013-03-21 |   null

I've tried a few different variations, but I'm striking out on this one:

select * 
from employees as cur 
where cur.valid_from < (
    select nx.valid_from from employees as nx 
    where 
       cur.source_id=nx.source_id 
       and nx.valid_from > cur.valid_from
);

And

update employees cur 
set cur.valid_to = (
    select nx.valid_from 
    from employees nx 
    where cur.source_id=nx.source_id 
          and nx.valid_from > cur.valid_from
);

Part of the problem with the subquery as I've written it is that it will return multiple rows. I just need the very first row that occurs after the valid_from of the current row. And the update statement MySQL complains that cur.valid_from cannot be used in the subquery.

I'm trying to find a query that would be portable between Oracle and MySQL. Can someone get me on the right track?

Best Answer

There a lot of differences between the two DBMS.

  • Oracle supports window fundtions, the MERGE statement and several other features that MySQL doesn't.
  • They have different datetime functions (but you are lucky here, - interval '1' day will work in both).
  • Different UPDATE syntax - apart from very simple, one table update statements.
    • MySQL supports a JOIN in the UPDATE statement, Oracle doesn't.
    • Oracle supports updatable "views" (derived tables), MySQL doesn't.
    • MySQL doesn't allow the updated table to be defined (again) in a subquery in the update WHERE.

Therefore, it will be very difficult - if possible at all - to write an update statement that will work in both dbms for youe complex case.

For Oracle, you can use either a MERGE statement, or the LEAD() function (altough I didn't manage to make it work in your case) or a self-join, or an inline subquery in an updatable "view":

SQLfiddle-1

update
( select id, valid_to, 
         ( select min(ee.valid_from)
           from employees ee
           where ee.object_id = e.object_id
             and ee.valid_from > e.valid_from
         ) as next_valid
  from employees e
) 
set valid_to = next_valid - interval '1' day ;

or with the more simple:

update employees e
set valid_to = 
   ( select min(valid_from)
     from employees u
     where u.object_id = e.object_id
       and u.valid_from > e.valid_from
   ) - interval '1' day ;

None of the above will work in mysql.

For MySQL it will have to be something more convoluted:

update
    employees e
  join
    ( select id, 
         ( select min(ee.valid_from)
           from employees ee
           where ee.object_id = ei.object_id
             and ee.valid_from > ei.valid_from
         ) as next_valid
      from employees ei
    ) upd
    on e.id = upd.id
set e.valid_to = upd.next_valid - interval 1 day ;

Another possibility for MySQL is using a variable (will update with an example later).

I think your only option, if you truly want something that works in both environments, is to break the problem into two actions:

  • 1, insert the values into a temporary table and
  • 2, do the actual update using that temp table.
Related Question