question

mfeuti avatar image
mfeuti asked

Update column with count of another column

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)
sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thomas, that looks to be exactly what I needed. Will test on dev server now. Much appreciated.
0 Likes 0 ·
Ok, that did work perfectly as I asked. Now I would like to do something slightly different. In cases where there are duplicates and/or triplicates I would like the first intance to be assigned a 1, the second instance a 2 and the third instance a 3. I have verified that there are no more than triplicates in the table. Your assistance is appreciated!
0 Likes 0 ·
ROW_NUMBER is what you're looking for.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, I just want to return the value of 2 where the count of the docID is 2. That may be the problem. There were not supposed to be multiple docID's in this tabel, I'm cleaning up someone else's old work and there are. There is another field called Location that is unique even if the docID is duplicated.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.