SQL sum statement invaild identifier

Question

I am trying to update a column of total quantity in each order using query

update orders 
set total_items = (SELECT L_QUANTITY AS "TOTAL" 
                   FROM 
                       (SELECT o_orderkey, SUM(L_Quantity) AS "TOTAL" 
                        FROM LINEITEM L 
                        JOIN ORDERS O ON O.O_ORDERKEY = L.L_orderkey 
                        WHERE L.L_orderkey > 1 
                        GROUP BY o_orderkey));

Oracle shows this error:

SQL Error: ORA-00904: "L_QUANTITY": invalid identifier
00904. 00000 - "%s: invalid identifier"

Are you using MySQL or Oracle? Please tag your question appropriately. — Gordon Linoff

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-03-13T16:55:03

To do what you describe, you just need a correlated subquery:

update orders o
    set total_items = (SELECT SUM(L_Quantity)
                       FROM LINEITEM L
                       WHERE O.O_ORDERKEY = L.L_orderkey AND L.L_orderkey > 1 
                      );

I'm not sure what the condition on L.L_Orderkey is supposed to be doing.

Your particular problem is because the innermost subquery has no column called L_QUANTITY (the sum is called TOTAL). But, if you fixed that, you would just have other problems, such as a scalar subquery returning too many rows.

SQL sum statement invaild identifier

1 Answers