mysql - 从SELECT更新（正确的重复值）(UPDATE FROM SELECT (correct duplicate values))

Question

The table TEST is wrong because it has duplicate values.

(表TEST是错误的，因为它具有重复的值。)

I need to update the relational table with an unique id for test and then delete the duplicated values.

(我需要使用唯一的ID更新关系表以进行测试，然后删除重复的值。)

TEST

(测试)

+----------+---------+
| id_test  |  name   |
+----------+---------+
|    1     |   gir   |
|    2     |   gir   |  
|    3     |   gir   |
|    4     |   ego   |
|    5     |   ego   |
|    6     |   iph   |
|    7     |   iph   |
+----------+---------+

PRODUCT_HAS_TEST

(PRODUCT_HAS_TEST)

+----------+---------+
| id_prod  | id_test |
+----------+---------+
|    3     |    1    |
|    1     |    2    |  
|    2     |    2    |
|    4     |    3    |
|    5     |    4    |
|    6     |    5    |
|    7     |    6    |
|    8     |    7    |
+----------+---------+

I have this query to find duplicate values:

(我有此查询以查找重复的值：)

SELECT GROUP_CONCAT(id_test) as ids, name, COUNT(*) c FROM test GROUP BY name HAVING c > 1;

Result:

(结果：)

+------+------+------+
| ids  | name |   c  |
+------+------+------+
| 4,5  | ego  |   2  |
|1,2,3 | gir  |   3  |
| 6,7  | iph  |   2  |
+------+------+------+

And another one to delete duplicate values from TEST:

(另一个从测试中删除重复值的方法：)

delete
from test using test,
    test e1
where test.id > e1.id
    and test.name = e1.name

But I need to update the table PRODUCT_HAS_TEST first and I don't know how to do it.

(但是我需要先更新表PRODUCT_HAS_TEST，但我不知道该怎么做。)

Expected result:

(预期结果：)

PRODUCT_HAS_TEST

(PRODUCT_HAS_TEST)

+----------+---------+
| id_prod  | id_test |
+----------+---------+
|    3     |    1    |
|    1     |    1    |  
|    2     |    1    |
|    4     |    1    |
|    5     |    4    |
|    6     |    4    |
|    7     |    6    |
|    8     |    6    |
+----------+---------+

TEST

(测试)

+----------+---------+
| id_test  |  name   |
+----------+---------+
|    1     |   gir   |
|    4     |   ego   |
|    6     |   iph   |
+----------+---------+

  ask by Giulia translate from so

Answer 1

it looks like you want to retain the min id_test value when id_test is duplicate,if it's true then you can use below srcipt :

(似乎您想retain the min id_test id_test重复时retain the min id_test值，如果为true，则可以在下面的srcipt中使用：)

logic :

(逻辑：)
1. using mysql update join with query for duplicate id_test mapping min_id_test then update id_test by min_id_test

(1.使用mysql update join查询duplicate id_test mapping min_id_test然后通过min_id_test更新id_test)
2. delete TEST table duplicate id_test when the id_test is not min_id_test

(2.当id_test不是min_id_test时，删除TEST表重复的id_test)

 CREATE TABLE TEST (`id_test` int, `name` varchar(3)) ; INSERT INTO TEST (`id_test`, `name`) VALUES (1, 'gir'),(2, 'gir'),(3, 'gir'),(4, 'ego'),(5, 'ego'),(6, 'iph'),(7, 'iph'); 

? (?)

? (?)

 CREATE TABLE PRODUCT_HAS_TEST (`id_prod` int, `id_test` int) ; INSERT INTO PRODUCT_HAS_TEST (`id_prod`, `id_test`) VALUES (3, 1),(1, 2),(2, 2),(4, 3),(5, 4),(6, 5),(7, 6),(8, 7); 

? (?)

? (?)

 UPDATE PRODUCT_HAS_TEST as T1 join ( select T1.*,T2.id_test as new_id_test from TEST T1 join ( select min(id_test) id_test,name from TEST where id_test is not null group by name ) T2 on T1.name = T2.name where T1.id_test <> T2.id_test )T2 on T1.id_test = T2.id_test set T1.id_test = T2.new_id_test where T2.new_id_test is not null ; 

? (?)

 DELETE FROM TEST as T where T.id_test not in ( select * from ( select distinct min(id_test) from TEST group by name ) T2 ); 

? (?)

 select * from PRODUCT_HAS_TEST; 

id_prod | (id_prod |)id_test (id_test)
------: | (------：|)------: (------：)
      3 | (3 |)1 (1个)
      1 | (1 |)1 (1个)
      2 | (2 |)1 (1个)
      4 | (4 |)1 (1个)
      5 | (5 |)4 (4)
      6 | (6 |)4 (4)
      7 | (7 |)6 (6)
      8 | (8 |)6 (6)

 select * from TEST; 

id_test | (id_test |)name (名称)
------: | (------：|):--- (：---)
      1 | (1 |)gir (吉尔) 
      4 | (4 |)ego (自我) 
      6 | (6 |)iph (IPH) 

db<>fiddle here (db <> 在这里拨弄)