12

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!

Charles
  • 853
  • 3
  • 8
  • 21

1 Answers1

39

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

Piotr Idzikowski
  • 713
  • 5
  • 13
  • This solution will not work on MySQL 5.7+ and will generate an error 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cdtmp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Gildonei Jun 27 '19 at 20:51