How to update in SQL to get separate tuples / not violate a unique constraint

Question

I have a mapping table with the only difference from the tuple (c_id, t_id) .

Here are 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 t_ids , then of course I violate the restriction using this statement:

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

Result:

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

Now I came up with the following:

 /* 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 get the following error:
You can't specify target table 'mapping_table' for update in FROM clause

My questions:

• What exactly is wrong here? Is the DELETE seen as an update and cannot be used in a WHERE ?
• Is this a more efficient way to do this?

Answer 1

The error you encounter is a feature 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 the problem, simply remove all identifiers except the minimum. I think this may 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 save the list of identifiers in a temporary table and use this 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 ) 

Answer 2

try it

 DELETE FROM mapping_table WHERE ( SELECT count(c_id) FROM mapping_table WHERE t_id = '$t1_id' OR t_id = '$t2_id' Having count(c_id) > 1 ); 

EDIT:

try this in your update status

  UPDATE mapping_table SET t_id = '$target_tid' WHERE t_id in (select t_id from mapping_table where t_id= '$t1_id' OR t_id = '$t2_id') 

Answer 3

I was looking for this solution. Performance is probably surprisingly low, but at least I found a working solution (and found out something).

 /* actually delete rows that will become duplicate after the update */ DELETE FROM mt1 USING mapping_table AS mt1 WHERE id IN ( /* sub-query to allow `mapping_table` in the DELETE statement */ SELECT * FROM ( /* select ids/rows with one t_id available */ SELECT id FROM mapping_table AS mt2 WHERE mt2.tag_id = $t1_id AND c_id IN ( /* select ids/rows with both t_id available */ SELECT c_id FROM mapping_table AS mt3 WHERE mt3.c_id = mt2.c_id AND mt3.tag_id = $t2_id) /* alias needed for every derived table */ ) as mres ) /* Update to merge t_ids */ UPDATE mapping_table SET t_id = '$target_id' WHERE t_id = '$t1_id' OR t_id = '$t2_id';