mysql - 如何在 SQL 中更新以获得不同的元组/不违反唯一约束

Question

我有一个映射表，在元组 (c_id, t_id) 上具有唯一约束。

下面是一些示例数据来说明这种情况:

id  c_id    t_id  
----------------
1   10      2
2   10      3
3   10      7
4   12      2
5   13      3

我为t_ids编写了一个合并函数(x,y -> z OR x,y -> x)。如果我的内容 (c_id) 具有两个 t_ids，那么我使用以下语句当然违反了约束:

UPDATE mapping_table
SET t_id = '$target_tid'
WHERE t_id = '$t1_id' OR t_id = '$t2_id';

结果将是:

id  c_id    t_id
----------------
1   10      4
2   10      4       /* violates unique constraint */
3   10      7

现在我想出了这个:

/* delete one of the duplicate entries */
DELETE FROM mapping_table
WHERE   ( SELECT count(c_id)
          FROM mapping_table
          WHERE t_id = '$t1_id' OR t_id = '$t2_id'
        ) > 1;

/* update the remaining row */
UPDATE mapping_table
SET t_id = '$target_tid'
WHERE t_id = '$t1_id' OR t_id = '$t2_id';

现在我收到以下错误:
您无法在 FROM 子句中指定要更新的目标表“mapping_table”

我的问题是:

1. 这里到底出了什么问题？ DELETE 语句是否被视为更新并且不能在 WHERE 子句中使用？
2. 有没有更有效的方法来做到这一点？

Answer 1

您遇到的错误是 MySQL 的一个特性。您可以使用一组子查询来解决这个问题:

DELETE FROM mapping_table
WHERE  (select *
        from ( SELECT count(c_id)
               FROM mapping_table
               WHERE t_id = '$t1_id' OR t_id = '$t2_id'
             ) > 1
        ) t

要解决您的问题，只需删除除最小值之外的所有 id。我认为这也可能有效:

delete from mapping_table
where id > (select minid from (select min(id) from mapping_table mt2
                               where mt2.c_id = mapping_table.c_id and
                                     mt2.t_id = mapping_table.t_id
                              )
           )

您还可以将 id 列表存储在临时表中，并在查询中使用它:

create temporary table minids as
     select c_id, t_id, min(id) as minid
     from mapping_table
     group by c_id, t_id;

delete from mapping_table
where exists (select 1 from minids
              where mt2.c_id = mapping_table.c_id and
                    mt2.t_id = mapping_table.t_id and
                    mt2.minid > mapping_table.id
             )