MySQL Update Subset Having

mysql having
Charles picture Charles · Nov 14, 2011 · Viewed 11.1k times · Source

I have three tables: contacts, domains, and contacts_domains, which form a many-to-many relationship.

I would like to run a query that updates the contacts_domains table, but only for domains that have exactly one contact.

I know how to SELECT the rows I'm interested in, but not how to UPDATE them.

SELECT domain_id, contact_id, dominant
FROM contacts_domains
GROUP BY domain_id
HAVING COUNT(contact_id) = 1

I want to set contacts_domains.dominant = 1 for all these results.

Thanks!

Answer

Piotr Idzikowski picture Piotr Idzikowski · Mar 25, 2014

I had problem like this. Try with joining with table make with select:

UPDATE contacts_domains cd, 
 (SELECT id FROM contacts_domains GROUP BY domain_id
   HAVING COUNT(contact_id) = 1) AS cdtmp
SET cd.dominant = 1
WHERE cd.id = cdtmp.id

Hope it will help

Related questions

MySQL - Using COUNT(*) in the WHERE clause

I am trying to accomplish the following in MySQL (see pseudo code) SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) > 10 ORDER BY lastupdated DESC Is there a way to do this without using a (SELECT...) in the WHERE clause because …

Read More
MySQL joins and COUNT(*) from another table

I have two tables: groups and group_members. The groups table contains all the information for each group, such as its ID, title, description, etc. In the group_members table, it lists all the members who are apart of each …

Read More
Can you use an alias in the WHERE clause in mysql?

I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than …

Read More