CODEWITHSUNDEEP
mysqlsqlcountsql-update
sebe
sebe

Reputation: 139

SQL Update with count for each row

I'm trying to update a field of a table with the count of occurrences from another table but am having no luck so far. This is what I have:

Update S
SET Hours = Hours + COUNT(S.ID)
FROM S, E
ON S.ID = E.ID
WHERE S.ID = E.ID
GROUP BY S.ID;

The program I'm being forced to use has terrible error handling and can only provide me with the description of the error being a syntax error. I've already successfully implemented a select statement that returns what I want:

Select COUNT(S.ID) 
FROM S, E
WHERE S.ID = E.ID
GROUP BY S.ID;

Am I missing something very simple here?

Upvotes: 1

Views: 3206

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

update s
   set hours =
       (select count(*) from e where e.id = s.id)

Upvotes: 2

juergen d
juergen d

Reputation: 204766

Update S
JOIN
(
  select id, COUNT(*) as cnt
  from e
  group by id
) TMP ON S.ID = TMP.ID
SET S.Hours = S.Hours + TMP.cnt

Upvotes: 1

Related Questions