List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:June 15 2007 4:57pm
Subject:Re: Hiding columns used in GROUP BY and HAVING clauses
View as plain text  
Hi Edward,

Edward Kay wrote:
> I have a table of addresses. Each address is associated with a primary
> entity and a primary entity can have n different addresses. For each primary
> entity, one address is marked as the main address.
> 
> I need a query to return all addresses that are the only address associated
> with the primary entity but aren't marked as the main address.
> 
> 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.

There are two issues.  1) MySQL optimizes IN() subqueries very badly, and 2) as you see 
a scalar subquery can only return one column here.  I suggest you rewrite it as a join:

update contact_address
	inner join (
		select address_id ...
	) as X using(address_id)
	set is_primary = 1;

Baron
Thread
Hiding columns used in GROUP BY and HAVING clausesEdward Kay15 Jun
  • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz15 Jun
  • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson15 Jun
    • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
      • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson18 Jun
        • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
          • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz18 Jun
            • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
          • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson18 Jun
            • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz18 Jun
              • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay19 Jun