> -----Original Message----- > From: Baron Schwartz [mailto:[EMAIL PROTECTED] > Sent: 18 June 2007 15:53 Sent: 18 June 2007 15:11 > > > > <snip> > > > >>>>> At the moment, I have this and it works: > >>>>> > >>>>> select * from contact_address > >>>>> group by primary_entity_id > >>>>> having count(primary_entity_id) = 1 > >>>>> and is_primary = 0; > >>>>> > >>>>> This is fine except I want to use the result in a sub-query. > >>>>> Since it returns two columns this doesn't work: > >>>>> > >>>>> update contact_address set is_primary = 1 where > >> address_id in ( > >>>>> select * from contact_address > >>>>> group by primary_entity_id > >>>>> having count(primary_entity_id) = 1 > >>>>> and is_primary = 0 > >>>>> ); > >>>>> > >>>>> Normally, I'd only return the address_id in the sub-SELECT, but I > >>>>> need the is_primary column for the HAVING clause. > >>>> I did some tests, and it looks like you can use aggregate functions > >>>> in your HAVING clause without actually selecting the column. So > >>>> "HAVING COUNT(primary_entity_id) = 1" should work even if you only > >>>> select address_id. > >>> Yes, that is true and it does work. > >>> > >>> What doesn't work however, is the extra 'AND is_primary = 0' HAVING > >>> clause. > >> Couldn't you move that up into a WHERE clause (still in the subquery)? > >> It's just a regular field comparison, so it doesn't have to be in the > >> HAVING clause. > > > > No, because that's not the same thing. Imagine the following data: > > > > address_id primary_entity_id is_primary > > ------------------------------------------- > > 1 293 0 > > 2 293 1 > > > > > > With my query above, the sub-query result set would be empty since > > count(primary_entity_id) = 2. > > > > If I move the is_primary = 0 requirement to a WHERE clause, > then the first > > row would be incorrectly updated since the group function would only be > > working on the sub-set of data (where is_primary = 0). > > > > It's a subtle but important difference. > > Then I think what you really want in your HAVING clause is "AND > MIN(is_primary) > = 0". > > Baron
But I'm first grouping by primary_entity_id and then only selecting the rows where count(primary_entity_id) = 1, so is_primary clause will only ever be working on one value. Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
