How to convert my select statement into update statement?

SELECT quotes.qid, SUM(qitems.net_cost_ext)
FROM quotes, qitems
WHERE quotes.qid = qite开发者_如何学Cms.qid
GROUP BY qitems.qid;

UPDATE quotes, qitems
SET quotes.net_cost_total = SUM(qitems.net_cost_ext)
WHERE quotes.qid = qitems.qid
GROUP BY qitems.qid;

The above select statements sums up the net cost for all the products on each quote and displays the sum by quote number.

I added a net_cost_total field to the quotes table. I want to update all the quotes with the net_cost totals from each quote. The query fails, it says the GROUP syntax is bad, I'm not sure how else to go about this.

Try this one:

UPDATE quotes AS q 
        JOIN (SELECT quotes.qid, 
                         SUM(qitems.net_cost_ext) AS SUM 
                  FROM   quotes, 
                         qitems 
                  WHERE  quotes.qid = qitems.qid 
                  GROUP  BY qitems.qid) AS d 
         ON d.qid = q.qid 
SET    q.net_cost_total = d.SUM 

Try:

UPDATE quotes
INNER JOIN (
    SELECT SUM(qitems.net_cost_ext) AS s
    FROM qitems
    GROUP BY qitems.qid
  ) q USING(qid)
SET net_cost_total = q.s

(inspired from the last MySQL comment here)

Using Standard SQL:

UPDATE quotes
   SET net_cost_total = (
                         SELECT SUM(net_cost_ext)
                           FROM qitems
                          WHERE quotes.qid = qitems.qid
                        )
 WHERE EXISTS (
               SELECT *
                 FROM qitems
                WHERE quotes.qid = qitems.qid
              );