mysql, the same table modifies one of the two fields based on two of the fields-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

mysql, the same table modifies one of the two fields based on two of the fields

迷茫 2017-05-18 10:50:49

656

There is a student table with table fields including id, name, parent_id, createDate, etc. Now we need to modify the names of students with the same name under the same parent_id based on parent_id, name, to ensure that they no longer exist under the same parent_id. How to deal with duplicate names

迷茫

业精于勤，荒于嬉;行成于思，毁于随。

reply all (1)

Ty802017-05-18 10:52:49 1 floor

select * from student where id not in(select min(id) from student group by parent_id,name)t

This is to find duplicate students. As for how to modify it, it’s up to you.

Supplement:
The modification is to sort according to createDate, and then add the sorted serial number after the name

update student t1 inner join (select idx,id from (select if(@m_last_parent_id=parent_id and @m_last_name=name,@m_i:=@m_i+1,@m_i:=0) idx, @m_last_parent_id:=parent_id,@m_last_name:=name,id,parent_id,name,createDate from student order by parent_id,name,createDate )m where idx>0 )t2 on t1.id=t2.id set t1.name=concat(t1.name,t2.idx);

This is the result of my own test: