SQL: Update a table column with data in column retrieved from a view in same procedure

I’m trying to do a select and update within the same local procedure. The idea is to retrieve the number of ordered products from a specific order in the view (Produktantal) and then updating the stock numbers (antal) from the table “produktlager” with the data retrieved from the view.

I’m receiving the follow error message Error: ER_BAD_FIELD_ERROR: Unknown column 'Produktantal' in 'field list'. I don’t seem to be available to access the column in the update part. How can I do this?

DROP PROCEDURE IF EXISTS update_stock_status;
DELIMITER ;;
CREATE PROCEDURE update_stock_status(
    p_orderid INT
)
BEGIN
    SELECT 
    Produktid,
    Orderid,
    Produktantal,
    Lagerantal,
    Hylla
    FROM v_products2order_w_products2lager
    WHERE Orderid = p_orderid;

    UPDATE produkt2lager
    SET
        antal = antal - Produktantal
    WHERE
        produktid = Produktid
    AND
        hylla = Hylla;
END
;;
DELIMITER ;

The view:

    DROP VIEW IF EXISTS v_products2order_w_products2lager;

    CREATE VIEW v_products2order_w_products2lager AS
    SELECT
        p2o.produktid AS "Produktid",
        p2o.orderid AS "Orderid",
        p2o.antal AS "Produktantal",
        p2l.antal AS "Lagerantal",
        p2l.hylla AS "Hylla"
    FROM
        produkt2order AS p2o
        LEFT OUTER JOIN produkt2lager AS p2l ON p2o.produktid = p2l.produktid;

Answer

The two statements in your procedure are executed independently, and the second statement cannot in any way access the results of the first one.

I suspect that you want the update ... join syntax:

UPDATE produkt2lager p
INNER JOIN v_products2order_w_products2lager o 
    ON o.produktid = p.produktid
    AND o.hylla = p.hylla
SET p.antal = p.antal - o.produktantal
WHERE o.oderid = p_order_id