Reputation: 1178
I have this SQL Code:
SELECT * FROM `clients_branches`
WHERE NULLIF(clients_branches.invoice_email, '') IS NULL
GROUP BY client_code
HAVING COUNT(*) = 1
It returns all rows which appears only once in the database, also it returns only the ones with no email set. Now I need to apply UPDATE function to all of this select statement. How could I do it? I need to set clients_branches.invoice_send to 0 for all these rows.
I can't seem to use HAVING COUNT on UPDATE statement like this:
UPDATE `clients_branches`
SET clients_branches.invoice_send = 0
WHERE NULLIF(clients_branches.invoice_email, '') IS NULL
HAVING COUNT(*) = 1
Without HAVING COUNT I will change all of the rows which repeats at least once in this table. And I need to change only the ones with count = 1.
Upvotes: 0
Views: 644
Reputation: 133410
You could use a join for allow the use of the table for update and the result of your query
update `clients_branches`
JOIN
(
select client_code, count(*)
FROM `clients_branches`
WHERE NULLIF(clients_branches.invoice_email, '') IS NULL
group by client_code
HAVING COUNT(*) = 1
) t on t.client_code = `clients_branches`.client_code
set clients_branches.invoice_send = 0
;
Upvotes: 1