Dennis Dennis - 5 months ago 16
MySQL Question

MySQL populating all valid_to and modified_by columns knowing all valid_from and created_by values

Here is an example of the table I am dealing with:

+----+-----------+-------+------------+---------------------+-------------+----------+
| id | object_id | value | created_by | valid_from | modified_by | valid_to |
+----+-----------+-------+------------+---------------------+-------------+----------+
| 1 | 333 | a | Paul | 2016-06-10 08:15:57 | | |
| 3 | 333 | b | Naomi | 2016-06-10 09:39:37 | | |
| 8 | 333 | c | Anne | 2016-06-10 09:46:09 | | |
+----+-----------+-------+------------+---------------------+-------------+----------+


What is a (preferably) single update statement that would update the table above to the table below?

+----+-----------+-------+------------+---------------------+-------------+---------------------+
| id | object_id | value | created_by | valid_from | modified_by | valid_to |
+----+-----------+-------+------------+---------------------+-------------+---------------------+
| 1 | 333 | a | Paul | 2016-06-10 08:15:57 | Naomi | 2016-06-10 09:39:37 |
| 3 | 333 | b | Naomi | 2016-06-10 09:39:37 | Anne | 2016-06-10 09:46:09 |
| 8 | 333 | c | Anne | 2016-06-10 09:46:09 | | |
+----+-----------+-------+------------+---------------------+-------------+---------------------+

Answer

You can do this using a join. For the data that you have:

update t join
       t t2
       on t2.id = t.id + 1
    set t.modified_by = t2.created_by and t.valid_to = t2.valid_from;

This assumes that the ids are sequential as in your example data.

EDIT:

If the values are sequential, you can use a subquery to get the previous one, and then use that for the join:

update t join
       (select t2.*,
               (select max(t3.id) from t t3 where t3.id < t2.id) as prev_id
        from t t2
       ) t2 
       on t2.prev_id = t.id + 1
    set t.modified_by = t2.created_by and t.valid_to = t2.valid_from;
Comments