How to update attribute based on result of aggregate function

I am new to mySQL. I want to update the order price of a table, based on the value of the retail price and the quantity of the product ordered. I have the following tables (simplified for this question):

Products:

ProductID
Retail_price

Sales_orders:

SalesOrdersID
Order_price //This is the derived attribute that I want to update when the below Sales_products table is updated.

Sales_products:

SalesOrdersID
ProductID
Quantity

The following code works in that I get the correct Order_price and it outputs in its own table. But I want to update the attribute Sales_orders.Order_price, not call out the order price in a table.

SELECT 
    sales_product.SalesOrdersID, 
    SUM(Quantity * Retail_price) as "Total price of order"
FROM 
    sales_product
    LEFT JOIN products ON sales_product.ProductID = products.ProductID
GROUP BY sales_product.SalesOrdersID

I’ve also tried a trigger, but this returns an error when I try to insert a new row in Sales_orders.

CREATE trigger find_order_price2
AFTER INSERT ON Sales_products
FOR EACH ROW

BEGIN
    DECLARE price int;
SELECT
    sales_product.SalesOrdersID, SUM(Quantity * Retail_price) INTO price
FROM
    sales_product
        LEFT JOIN
        products ON sales_product.ProductID = products.ProductID;
UPDATE sales_orders
set Order_price = price;
END;
$$
DELIMITER ;

Error returned :

Error Code: 1222. The used SELECT statements have a different number of columns

Hope somebody can help with this?

Answer

This is a trigger, so I expect it to be using new or old.

This would usually be done incrementally:

CREATE trigger trig_sales_products_update_price
AFTER INSERT ON Sales_products
FOR EACH ROW
BEGIN
    UPDATE sales_order so JOIN
           product p
           ON p.product_id = new.product_id
        SET so.order_price = so.order_price + new.quantity * p.retail_price
        WHERE so.SalesOrderId = new.SalesOrderId
END;
$$
DELIMITER ;

You can recalculate the entire value if you like:

CREATE trigger trig_sales_products_update_price
AFTER INSERT ON Sales_products
FOR EACH ROW
BEGIN
    UPDATE sales_order so JOIN
           (SELECT sp.SalesOrderId,
                   SUM(sp.quantity * p.retail_price) as new_total
            FROM sales_products sp
                 product p
                 ON p.product_id = sp.product_id
            WHERE sp.SalesOrderId = new.SalesOrderId
            GROUP BY sp.SalesOrderId
           ) sp
           ON so.SalesOrderId = sp.SalesOrderId
        SET so.order_price = sp.new_total
END;
$$
DELIMITER ;

However, triggers are usually done incrementally.

Remember that if you do this, you will also need update and delete triggers as well. That is why it is simpler to calculate these values “on-the-fly” rather than maintaining them using triggers.