Menu
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!
906
For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.
MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.
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
163
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
