What is an easy query to fix up historical columns valid_from and valid_to? 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 &lt; (
    select nx.valid_from from employees as nx 
    where 
       cur.source_id=nx.source_id 
       and nx.valid_from &gt; 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 &gt; 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?
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 &gt; 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 &gt; 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 &gt; 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.