update a field with count

[JazzMaan]

I am trying to update PurOrders field "num_filled" with count of orders there are for every PurOrder.

I get error:
"An aggregate may not appear in the set list of an UPDATE statement."


UPDATE PurOrders
SET num_filled = count(*)
FROM Orders o INNER JOIN
PurOrders p ON o.external_id = p.external_id
group by p.external_id

 

[bborissov]

UPDATE PurOrders
SET num_filled = SELECT count(*)
                       FROM Orders o
                       INNER JOIN PurOrders p ON
                             o.external_id = p.external_id
                        GROUP BY p.external_id

(not tested at all)

Borislav Borissov

 

[JazzMaan]

that didnt work ..

but this does:

UPDATE PurORDERS
SET num_filled = b.numAssgn
FROM PurORDERS a INNER JOIN (SELECT external_job_order_id,
COUNT(*) as numAssgn FROM orders GROUP BY
external_id) b
ON a.external_id = b.external_id