> -----Original Message----- > From: Dan Nelson [mailto:[EMAIL PROTECTED] > 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. Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
