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

2022-01-17 00:00:00 merge sql-update duplicates mysql unique-constraint

我有一个在元组 (c_id, t_id) 上有唯一约束的映射表.

I have a mapping table with a unique contraint on the tuple (c_id, t_id).

这里有一些示例数据来说明这种情况:

Here's some sample data to illustrate the situation: 

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，那么我当然违反了使用以下语句的约束:

I wrote a merge function for t_ids (x,y -> z OR x,y -> x). If my content (c_id) has both t_ids, then I'm of course violating the constraint by using this statement: 

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. 这还有什么更有效的方法吗?
  1. What's exactly wrong here? Is the DELETE statement seen as an update and cannot be used in the WHERE clause?
  2. This there any more efficient way to do this?

推荐答案

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

The error that you are having is a peculiarity of MySQL. You can get around this with a double set of subqueries:

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.我认为这也可能有效:

To fix your problem though, just remove all ids except for the minimum. I think this might also work:

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 列表存储在临时表中，并在查询中使用它:

You can also store the list of ids in a temporary table, and use that in the query:

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
             )

相关文章