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.
Any ideas on how to achieve this?
Thanks,
Edward
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
