July 31, 2003 at 4:54 am
I have 2 tables with a one-to-many join. I would like to update a column in one table with the number of occurences of the ID in the other table. Something like this:
UPDATE Table1
SET Table1.NumOfImages = COUNT(Table2.KeywordID)
FROM Table1, Table2
WHERE Table1.KeywordID = Table2.KeywordID
When I execute this I get the following error: An aggregate may not appear in the set list of an update statement. Any help appreciated!
John
July 31, 2003 at 5:21 am
Try using subquery instead
UPDATE Table1
SET Table1.NumOfImages = (Select COUNT (Table2.KeywordID)
FROM Table2
WHERE Table2.KeywordID = Table1.KeywordID)
FROM Table1
July 31, 2003 at 5:31 am
Or alternately
UPDATE Table1
SET Table1.NumOfImages = SubQ.Cnt
FROM Table1
INNER JOIN
(SELECT
COUNT(IQ.KeywordID) Cnt,
IQ.KeywordID
FROM
Table2 IQ
GROUP BY
IQ.KeywordID
) SubQ
WHERE Table1.KeywordID = SubQ.KeywordID
July 31, 2003 at 5:39 am
Or continue to use the join syntax as in:
UPDATE Table1
SET Table1.NumOfImages = temp1.NumOfImages
FROM Table1
INNER JOIN
(SELECT KeywordID, count(*) as NumOfImages
FROM Table2
GROUP BY KeywordID) temp1
ON Table1.KeywordID = temp1.KeywordID
Sorry for the double post. Was a bit too slow...
Edited by - NPeeters on 07/31/2003 05:39:10 AM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply