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
