我有这个SQL代码:
SELECT * FROM `clients_branches`
WHERE NULLIF(clients_branches.invoice_email, '') IS NULL
GROUP BY client_code
HAVING COUNT(*) = 1
它返回所有在数据库中仅出现一次的行,并且仅返回未设置电子邮件的行。现在,我需要将UPDATE函数应用于所有此select语句。我该怎么办?我需要将所有这些行的clients_branches.invoice_send设置为0。
我似乎无法在这样的UPDATE语句上使用HAVING COUNT:
UPDATE `clients_branches`
SET clients_branches.invoice_send = 0
WHERE NULLIF(clients_branches.invoice_email, '') IS NULL
HAVING COUNT(*) = 1
如果没有HAVING COUNT,我将更改此表中至少重复一次的所有行。而且我只需要更改count = 1的那些。
您可以使用联接来允许使用表进行更新和查询结果
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
;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句