mysql - 如何仅在 WHERE COUNT(使用 GROUP BY 的子查询)=1 时更新值？

Question

当它是组中唯一的行时，我试图通过单个查询将行值设置为 1。

当前表 (CATEGORY_BRAND):

|categoryId | brandId | featured |
|-----------+---------+----------|
| 1         | 100     | -1       |
| 1         | 101     | -1       |
| 1         | 102     | -1       |
| 2         | 100     | -1       |
| 3         | 110     | -1       |
| 3         | 111     | -1       |
| 4         | 102     | -1       |
| 5         | 101     | -1       |
| 6         | 100     | -1       |
| 6         | 110     | -1       |
| ...       | ...     | ...      |

希望的结果 (CATEGORY_BRAND):

|categoryId | brandId | featured |
|-----------+---------+----------|
| 1         | 100     | -1       |
| 1         | 101     | -1       |
| 1         | 102     | -1       |
| 2         | 100     | 1        |
| 3         | 110     | -1       |
| 3         | 111     | -1       |
| 4         | 102     | 1        |
| 5         | 101     | 1        |
| 6         | 100     | -1       |
| 6         | 110     | -1       |
| ...       | ...     | ...      |

(如果这是重复的，我最诚挚的歉意，我找不到其他人做这样的更新。)

Answer 1

这可能不是最高效的，但应该可以工作(尽管您应该先对其进行测试)。

UPDATE category_brand JOIN (
    SELECT categoryId
    FROM category_brand
    GROUP BY categoryId
    HAVING COUNT(categoryId) = 1)
  AS single_cbs USING(categoryId) SET category_brand.featured = 1;