如何在SQL中进行更新以获得不同的元组/不会违反唯一约束 [英] How to update in SQL to get distinct tuples / not to violate a unique constraint

问题描述

我有一个映射表，在元组（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 / *违反唯一约束* / 
 3 10 7 
  

现在我想出了这个：

  / *删除其中一个重复项* / 
 DELETE FROM mapping_table 
 WHERE（SELECT count（c_id）
 FROM mapping_table 
 WHERE t_id ='$ t1_id'OR t_id ='$ t2_id'
）> 1; 
 
 / *更新剩余行* / 
 UPDATE mapping_table 
 SET t_id ='$ target_tid'
 WHERE t_id ='$ t1_id'OR t_id ='$ t2_id ; 
  

现在我收到以下错误：

您不能指定目标表'mapping_table'用于FROM子句中的更新

我的问题是：

1. 这里有什么错误？是否将 DELETE 语句视为更新，不能在 WHERE 子句中使用？


2. 这有更有效的方法吗？

解决方案>

你所遇到的错误是MySQL的特殊之处。您可以通过一组两个子查询来解决这个问题：

  DELETE FROM mapping_table 
 WHERE（select * 
从（SELECT count（c_id）
 FROM mapping_table 
 WHERE t_id ='$ t1_id'OR t_id ='$ t2_id'
）> 1 
）t 
  

要解决您的问题，只需删除所有ids，除了最低限度。我认为这也可能会起作用：

 从mapping_table删除
其中id> （select minid from（select min（id）from mapping_table mt2 
其中mt2.c_id = mapping_table.c_id和
 mt2.t_id = mapping_table.t_id 
）
）
  

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

 创建临时表minids为
从mapping_table中选择c_id，t_id，min（id）作为minid 
 
 group by c_id，t_id; 
 
从mapping_table删除
其中存在（从minids 
中选择1，其中mt2.c_id = mapping_table.c_id和
 mt2.t_id = mapping_table.t_id和
 mt2.minid> mapping_table.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

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';

The result would be:

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

Now I came up with this:

/* 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';

Now I'm getting the following error:
You can't specify target table 'mapping_table' for update in FROM clause

My questions are:

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?

解决方案

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

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
                              )
           )

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
             )

这篇关于如何在SQL中进行更新以获得不同的元组/不会违反唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！