MYSQL - update a column from a SELECT result-CodePudding

I am trying to use the result in the column count from this query

SELECT `id_order`, COUNT(*) AS count
FROM `gf_order_detail`
GROUP BY `id_order`

to update the column gf_comp.nbr

I have tried this:

UPDATE `gf_comp`
SET `nbr` =
(
SELECT `id_order`, COUNT(*) AS count
FROM `gf_order_detail`
GROUP BY `id_order`)
WHERE gf_comp.ID=id_order;

But I get the error #1054 - Unknown column 'id_order' in 'where clause'

CodePudding user response：

Put your where clause inside the subquery:

UPDATE `gf_comp` g
SET g.`nbr` =
(
SELECT COUNT(*) AS count
FROM `gf_order_detail`
WHERE g.ID=id_order
GROUP BY `id_order`);

CodePudding user response：

You must join your query to gf_comp:

UPDATE gf_comp c
INNER JOIN (
  SELECT id_order, COUNT(*) AS count
  FROM gf_order_detail
  GROUP BY id_order
) o ON o.id_order = c.ID
SET c.nbr = o.count;