Alter table add column as SELECT

Question

So I wanted to add new column to my table made from a select using two other tables. I tried a query like this:

ALTER TABLE order ADD cost DECIMAL(5,2) AS (SELECT SUM(price*pieces) FROM book JOIN details ON id_book=book_id GROUP BY order_id);

And I get error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sum(price*pieces) from book join details on id_book=book_id group by order_id ' at line 1

My other tables look like this:

CREATE TABLE details (
id_d INT(10) NOT NULL AUTO_INCREMENT,
book_id INT(10) DEFAULT NULL,
order_id INT(10) DEFAULT NULL,
pieces INT(10) DEFAULT NULL
...

CREATE TABLE book (
id_book INT(10) NOT NULL AUTO_INCREMENT,
price DECIMAL(5,2) DEFAULT NULL,
...

This SELECT SUM(price*pieces) FROM book JOIN details ON id_book=book_id GROUP BY order_id; works but I really don't know how to add this as a new column :(

Answer 1

You can't specify the data to fill the column in the ALTER TABLE statement. That needs to be done in a separate UPDATE statement.

ALTER TABLE order ADD cost DECIMAL(5,2) DEFAULT 0;

UPDATE order AS o
JOIN (
    SELECT d.order_id, SUM(d.pieces, * b.price) AS cost
    FROM details AS d
    JOIN book AS b ON d.book_id = b.id_book
    GROUP BY d.order_id) AS d ON d.order_id = o.order_id
SET o.cost = d.cost

Answer 2

Question's been posted for some time now and the funny thing is I also thought syntax was a bit odd when I saw it in my project and I ended up here looking for some explanation. I understand now that while it may not be possible to use both ALTER TABLE and a fill-in value statement, one can declare a column field as a 'COMPUTED' type meaning you can declare a function that will execute on the fly in every select statement, I leave here a sample code reference using this syntax for whoever finds it useful:

ALTER TABLE ACCOUNTS ADD ACCOUNT_CASH AS get_VALUE ('CASH', CURRENCY, BUSINESS_TYPE, STATUS );

Though in most of the cases, a trigger would be a better approach.