Registered User

First of all - thank you very much for providing the CREATE TABLE and INSERT statements. Those really help!

If you have MySQL version < 8.0.2, then you could find the previous level by joining the table with itself.

Let's say for a given date in events table, I have a set of records S that includes

Then the

The following query fetches that information.For each date in events table, it fetches the current or previous maximum date with in_event=1.

Then all you have to do is - plug in this statement in MySQL's UPDATE..JOIN syntax to update the events table.

Unfortunately, to get all this information, we join and scan the events table 3 times and then one more time to update it.

That is highly inefficient. So the SQL standard came up with "analytic" or "window" functions to reduce the unnecessary table scans.

If you have MySQL version 8.0.2 or higher, then it supports window functions and you could use them to solve your problem.

I am not sure if you know about them, so I'll give a high level view to show how those could be used.

Let's say we divide the data into "batches".

For example:

1) Dates from '2018-09-03' to '2018-09-04' are in one batch.

2) Dates from '2018-08-21' to '2018-08-31' are in another batch.

3) Dates from '2018-08-07' to '2018-08-20' are in yet another batch. And so on...

Then the first date in each batch (the least date) is the one we are looking for.

We identify it and pick up its level and set that as the delta for all records in that batch.

Now to generate "batches", we only need generate a running total. The SUM() analytic function could do that for us.

(Note that this is not the SUM

All aggregate functions like MAX, MIN, COUNT etc. have their corresponding analytic versions.)

Once I have the batches, I find the first date in each batch using the "FIRST_VALUE()" analytic function.

The data has to be divided into batches which is specified by the "partition by" clause.

Now that I have all the necessary information, I plug in this query in the UPDATE..JOIN syntax to update the table.

One last observation: in your workaround solution, if you change "order by date desc" to "order by date" or "order by date asc", then it copies the data in the desired direction.

Hope that helps!

If you have MySQL version < 8.0.2, then you could find the previous level by joining the table with itself.

Let's say for a given date in events table, I have a set of records S that includes

**and having***that date and all previous dates**.***in_event = 1**Then the

*and its level value in the set S is the one I want.***maximum date**The following query fetches that information.For each date in events table, it fetches the current or previous maximum date with in_event=1.

Then all you have to do is - plug in this statement in MySQL's UPDATE..JOIN syntax to update the events table.

Unfortunately, to get all this information, we join and scan the events table 3 times and then one more time to update it.

That is highly inefficient. So the SQL standard came up with "analytic" or "window" functions to reduce the unnecessary table scans.

If you have MySQL version 8.0.2 or higher, then it supports window functions and you could use them to solve your problem.

I am not sure if you know about them, so I'll give a high level view to show how those could be used.

Let's say we divide the data into "batches".

For example:

1) Dates from '2018-09-03' to '2018-09-04' are in one batch.

2) Dates from '2018-08-21' to '2018-08-31' are in another batch.

3) Dates from '2018-08-07' to '2018-08-20' are in yet another batch. And so on...

Then the first date in each batch (the least date) is the one we are looking for.

We identify it and pick up its level and set that as the delta for all records in that batch.

Now to generate "batches", we only need generate a running total. The SUM() analytic function could do that for us.

(Note that this is not the SUM

*aggregate*function that has the GROUP BY clause to go with it. But it is analogous to it.All aggregate functions like MAX, MIN, COUNT etc. have their corresponding analytic versions.)

Once I have the batches, I find the first date in each batch using the "FIRST_VALUE()" analytic function.

The data has to be divided into batches which is specified by the "partition by" clause.

Now that I have all the necessary information, I plug in this query in the UPDATE..JOIN syntax to update the table.

One last observation: in your workaround solution, if you change "order by date desc" to "order by date" or "order by date asc", then it copies the data in the desired direction.

Hope that helps!