I am new to MySQL and I am trying to add a calculated value to one of my tables.
I have two MySQL tables with information about orders from my website. The first table orders contains metadata about the order and my second table order_lines contains the details for each line of the order.
Here is an example of my order_lines table
+----------+---------------+----------+
| order_id | item | leadtime |
+----------+---------------+----------+
| 1 | iPhone 8 | 22 |
+----------+---------------+----------+
| 1 | Thinkpad T450 | 30 |
+----------+---------------+----------+
| 1 | SSD 1TB | 10 |
+----------+---------------+----------+
I want to make a new column in a seperate table which shows the maximum value of leadtime for each order_id. It would look something like this:
+----------+--------------+
| order_id | max_leadtime |
+----------+--------------+
| 1 | 30 |
+----------+--------------+
As you can see, the max_leadtime for order_id 1 is 30 because 30 is the maximum value in the order_lines table where order_id is equal to 1.
I am trying to achieve this with
ALTER TABLE
orders ADD COLUMN max_leadtime INTEGER GENERATED ALWAYS AS
(SELECT
MAX(leadtime)
FROM
order_lines
WHERE
order_lines.order_id = orders.order_id)
) STORED
This throws the error "A new statement was found, but no delimiter between it and the previous one. (near "SELECT" at position 81)". This is confusing to me though as there is no previous statement.
Does anyone know how I could successfully generate this new column as a calculated value.
