Level 39
a alias is not known inside 2nd query.
Why do you open a 2nd thread? What about the first one?
Apr 26, 2022
6
Level 1
Access parent table column from subquery in update query
I have an average table and I want to update with subquery, but return this error thanks for help
query :
UPDATE averages a,
(SELECT SUM(qty*cost) AS total_cost
FROM averages
WHERE averages.parent_id = a.product_id
AND averages.invoice_id = a.invoice_id) as parent
SET cost = parent.total_cost
error :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.product_id' in 'where clause' (SQL: UPDATE averages a, (SELECT SUM(qty) AS total_cost FROM averages WHERE averages.parent_id = a.product_id AND averages.invoice_id = a.invoice_id) as parent SET cost = parent.total_cost)
Level 1
@sr57 because I have many subquery and many condition by record type how i can do it
Level 1
thanks bro how I can do it ?
Level 39
by answering to my question.
Level 39
because I have many subquery and many condition by record type how i can do it
OK, but step by step, first can you conclude on
https://laracasts.com/discuss/channels/laravel/update-column-value-with-subquery-itself
Level 1
@sr57 thanks bro for replay i have this query to calculate average product and i want update record depends on SUM() of previous record
UPDATE averages a,
(SELECT SUM(qty*cost) AS total_cost
FROM averages
WHERE type = 'sale'
WHERE averages.parent_id = a.product_id
WHERE averages.order_number < a.order_number
AND averages.invoice_id = a.invoice_id) as sale
(SELECT SUM(qty*cost) AS total_cost
FROM averages
WHERE type = 'production_created'
WHERE averages.parent_id = a.product_id
AND averages.invoice_id = a.invoice_id) as parent
SET cost = Case a.type
WHEN 'sale' THEN sale.total_cost
WHEN 'production_created' THEN parent.total_cost
END
Please or to participate in this conversation.