Hello I'm trying to update a column with the count of another column. Below is what I'm using and I think I know why it is not working but I can't seem to get the syntax correct. Your help is most appreciated. update image_archive set image_count = (select count(docID)from Image_archive group by docID having count(docID) = 2)
Seems to me as though what you want is something like this: UPDATE image_archive SET image_count = ia.imagecount FROM (SELECT doc_id, count(*) AS imagecount FROM image_archive GROUP BY doc_id HAVING count(*) = 2 ) ia WHERE image_archive.doc_id = ia.doc_id What if there are 3 doc_id's that match? Change the HAVING clause to HAVING count(*) >= 2 Something like that, anyway. Untested code - do not trust in your production environment without trying it out first.
Two things wrong with that query. 1. the subquery could return many rows, and yet I presume you only want one value 2. the subquery is only going to return the value '2' Seems like there are multiple docID in the table - what uniquely identifies a row in image_archive then?