mysql - 更新与另一个表的副本关联的所有行

Question

MySQL 5.6

我们有两个表:cars 和 views。

Cars            Views
---+-------     ---+-------
id | desc       id | car_id
---+-------     ---+-------
1  | desc1      1  | 1
2  | Desc1      2  | 2
3  | desc2      3  | 3

问题出在表格车厢中的 desc 字段。该行必须是唯一的，但不幸的是，我们允许用户填写大写值，这使我们陷入了(根据上面的示例)两个重复行的情况:desc1 和 Desc1 。

解决这个问题的方法是DELETE 重复的汽车，只保留第一辆。我们知道如何处理。

我们的问题出现在更新相关表时，其中一些 View 与具有重复 desc 的汽车相关联(例如，将要删除的汽车)。应更新这些 View 以分配给第一辆重复的汽车(在本例中为汽车 ID #1)

在 UPDATE 之后，我们希望在 View 中产生这样的结果:

Views
---+-------
id | car_id
1  | 1
2  | 1
3  | 3

我们能够获取所有重复汽车的 ID 并处理删除，但我们受困于此 UPDATE。

Answer 1

解决方案是创建一个映射表，其中包含描述 id 的前/后值。

结果应该是这样的:

Before | After
---------------
1      | 1
2      | 1
3      | 3

该表可以用这样的东西创建:

SELECT
    cars.id AS before_id,
    fixed.lowest_id AS after_id
FROM cars
JOIN (
    -- The lowest id value for each duplicate description
    SELECT
        MIN(id) AS lowest_id,
        LOWER(desc) AS lower_desc
    FROM cars
    GROUP BY LOWER(desc)
) fixed
ON LOWER(cars.desc) = fixed.lower_desc

然后您可以让您的 View 与该映射表匹配以提取新的“正确”id 值。