> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]
> 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
