将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字段设置为空，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 (第2行)。我通过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