[RESOLVED] All records based on aggregate function

My SQL is a bit rusty and I'm trying to clean up some data.

What i need is to do is UPDATE some of the records in a table where
the COUNT for [field1] = 2.

My initial select query gives me the set to look through, but now what?
I need to change some [tbl_item].itemvalue in this set, where itemvalue = 'baddata' make it 'gooddata'


select itemcode,count(itemcode) from tbl_item
group by itemcode
having count(itemcode)=2


in other words, there are some records where itemvalue='baddata' and we do not want to change them, unless that itemcode is in the table exactly twice.

Do you want to change both imtecodes or only one of them?

I want to change itemvalue, when itemvalue is 'baddata' only in records where that record's itemcode occurs exactly 2 times in the table.

Got it.

I have to use "WHERE [] in"

Select * FROM tbl_item
Where itemcode in
(select itemcode,count(itemcode) from tbl_item
group by itemcode
having count(itemcode)=2)

This give me the set of records that is eligible for updating.
Now, I'll just put an update around this, check for condition, and I'll be fine.


I'd change the title to [resolved] but my edit function does not allow me to.

The easy way is to click on "Thread tools" just above the first post in this thread, then "Mark thread resolved".

Another easy way would be to give me access to "my" own thread titles.
:)


RESOLVED! thanks

You should have that already - by clicking on "Edit" in the first post, then "Go Advanced".

However, it is easier (and doesn't leave an "edited by" message) to use the feature provided.