mysql - 将 SELECT 语句转换为其相应的 UPDATE 语句

Question

我有一个 SQL 查询，用于计算需要使用 UPDATE 语句更新其信息的确切人口。但它包含一些复杂的连接，我不确定如何在 UPDATE 语句中重新创建该语句。有什么帮助吗？

我的查询在这里:

select distinct c.id
from implementation.tt_ngma_exclude_emails nee
join customer c on nee.util_account_id = c.util_internal_id
join customer_notification_contact cnc on cnc.customer_id = c.id
left join customer_notification_contact_audit cnca on cnca.customer_id = c.id
where cnc.changed_on = '2015-11-15 12:30:02';

此处的目标是更新 customer_notification_contact 表中的特定字段，而不是我从中选择的实现表。我想将 cnc 表中的 email 字段设置为 NULL，只要 cnc.customer_id = c.id

这是我的尝试，但似乎不起作用:

UPDATE customer_notification_contact cnc
(select distinct cnc.customer_id opower_customer_id
from implementation.tt_ngma_exclude_emails nee
join customer c on nee.util_account_id = c.util_internal_id
join customer_notification_contact cnc on cnc.customer_id = c.id
where cnc.changed_on = '2015-11-15 12:30:02'
) T2
SET cnc.email = NULL
WHERE cnc.customer_id = T2.opower_customer_id;

Answer 1

请尝试一下，只需将 T1.SOME_COLUMN (最后第二行)替换为您要更新的实际列名称。我添加了 GROUP BY CNC.CUSTOMER_ID ，因为当您更新多行时，您应该知道该计数属于哪个客户。除非您尝试更新具有相同计数的所有行，但我假设您不想这样做。

UPDATE customer_notification_contact T1,
(
select count(distinct c.id) AS MY_COUNT,CNC.CUSTOMER_ID
from implementation.tt_ngma_exclude_emails nee
join customer c on nee.util_account_id = c.util_internal_id
join customer_notification_contact cnc on cnc.customer_id = c.id
left join customer_notification_contact_audit cnca on cnca.customer_id = c.id
where cnc.changed_on = '2015-11-15 12:30:02'
GROUP BY CNC.CUSTOMER_ID
)T2
SET T1.SOME_COLUMN = T2.MY_COUNT
WHERE T1.CUSTOMER_ID = T2.CUSTOMER_ID

更新在看到更新的问题后，这应该是查询。

UPDATE customer_notification_contact T1,
(
select distinct c.id
  from implementation.tt_ngma_exclude_emails nee
  join customer c on nee.util_account_id = c.util_internal_id
  join customer_notification_contact cnc on cnc.customer_id = c.id
  left join customer_notification_contact_audit cnca on cnca.customer_id = c.id
  where cnc.changed_on = '2015-11-15 12:30:02'
)T2
SET T1.EMAIL = NULL
WHERE T1.CUSTOMER_ID = T2.id