MySQL:更新 SET 和 WHERE 都由一个子选择确定

Question

我不知道该怎么做..

我有一个这样的表:

account_categories
--------------------
id  | description
--------------------
34  | Home Services
35  | Home Services
36  | Home Services
39  | Home Design
40  | Home Design

我有另一个引用 account_categories.id 的表(帐户)，它使用了上述所有值。 :/

我想扁平化 account_categories，因此我需要从 account_categories 中选择一个副本并更新帐户，以便所有副本都使用一个选定值。

例如，我需要把这个:

accounts
---------------------
id  | accountCategory
---------------------
1   | 34
2   | 35
3   | 36
4   | 39
5   | 40

进入这个:

accounts
---------------------
id  | accountCategory
---------------------
1   | 34
2   | 34
3   | 34
4   | 39
5   | 39

我可以像这样从帐户类别中选择一个 ID 和不同的描述:

SELECT DISTINCT (description), id
FROM crmalpha.account_categories
GROUP BY description

但我想下一步是做这样的事情:

for ( row in ( SELECT DISTINCT (description), id FROM crmalpha.account_categories GROUP BY description ) ) {
    UPDATE crmalpha.accounts SET accountCategory = $row['id'] WHERE accountCategory IN ( SELECT id FROM crmalpha.account_categories WHERE description = $row['description] )
}

请原谅 for 循环和 php 变量伪代码，我只是想从逻辑上考虑一下。我不知道如何在纯 SQL 中完成此操作。

有什么想法吗？

PS., 之后，我将遍历并从 account_categories 中删除 accounts 表中未使用 ID 的每一行。

Answer 1

当我针对您在上面发布的测试数据进行尝试时，这有效。也就是说，在进行任何此类大规模清理时，我建议先复制表格。还要在发出 UPDATE 之后和发出 COMMIT 之前检查结果。

这是查询:

UPDATE Accounts acct
INNER JOIN Account_Categories cat ON acct.AccountCategory = cat.id
INNER JOIN (
  SELECT MIN(id) AS NewID, Description
  FROM Account_Categories
  GROUP BY Description) NewCat ON cat.Description = NewCat.Description
SET acct.AccountCategory = NewCat.NewID

一些解释:

• 子查询 (SELECT MIN(id)...) 为每个描述获取一个 ID 值(最小值)。
• 第一个连接(到 Account_Categories)将每个帐户与其类别相关联，唯一目的是提供可用的描述。
• 第二个连接(到子查询)将帐户的现有描述与扁平化/去重复描述及其 ID 表相关联。