add auto generate column with correlated subqueries in mysql

I want to add a column that auto generates value depending on other tables. There are 2 tables to work with.

Table “finance” :

revenue | d 
1       |2020-07-01
2       |2020-10-02
3       |2020-01-09

Table “price” :

price | d
1     | 2020-10-01
2     | 2020-10-02
3     | 2020-10-03
4     | 2020-01-08
5     | 2020-10-09

And by executing this command

select p.*,
(select f.revenue * 10 from finance f where f.d <= p.d order by d desc limit 1) revenue_X_10
from price p

I get this result

price | d         | revenue x 10
1       2020-10-01    10
2       2020-10-02    20
3       2020-10-03    20
4       2021-01-08    20
5       2021-01-09    30

I wanted to make the revenue_x_10 auto generated by doing something like this.

ALTER TABLE price 
ADD COLUMN revx10 FLOAT
GENERATED ALWAYS AS (select 10*f.revenue from finance f where p.d > f.d order by d desc limit 1) from price p;

But looks like I can only put columns from the same table then calculate new ones on them. How can I make the revenue_x_10 as a column generated automatically on the “price” table.

Reason why I want to do something like this is because performance issues. I’m working with relatively big dataset, like a couple thousand row on the “price” and dozen rows on the “finance”. And sometimes I need to do this for couple thousand tables. By using queries it’s just too slow to do.

Answer

As mentioned by gordon, it can not refer to other tables while it comes to auto generate.

You can create a view with not exists as follows:

create view v_price as
    select p.*,
           f.revenue * 10  as revenue_X_10
      from price p 
      join finance f on f.d <= p.d
     Where not exists 
         (select 1 from finance ff Where ff.d > f.d and ff.d <= p.d)