我正在嘗試使用count此查詢列中的結果
SELECT `id_order`, COUNT(*) AS count
FROM `gf_order_detail`
GROUP BY `id_order`
更新列gf_comp.nbr
我試過這個：
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;
但我收到錯誤 #1054 - 'where 子句'中的未知列 'id_order'
uj5u.com熱心網友回復：
將 where 子句放在子查詢中：
UPDATE `gf_comp` g
SET g.`nbr` =
(
SELECT COUNT(*) AS count
FROM `gf_order_detail`
WHERE g.ID=id_order
GROUP BY `id_order`);
uj5u.com熱心網友回復：
您必須將您的查詢加入到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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/492925.html
標籤：mysql