How to prevent duplicate entry key when update

Problem explain

I won’t update the last primary key of the 3 primary key concatenate. But the problem is sometimes the first and second primary key was the same for multiple records. And in this case, when I set my new value I have a duplicate entry key even I use sub-request to avoid that problem.

Some Code

Schemas

create table llx_element_contact
(
    rowid             int auto_increment
        primary key,
    datecreate        datetime           null,
    statut            smallint default 5 null,
    element_id        int                not null,
    fk_c_type_contact int                not null,
    fk_socpeople      int                not null,
    constraint idx_element_contact_idx1
        unique (element_id, fk_c_type_contact, fk_socpeople)
)

Update request

this request return duplicate key error

update llx_element_contact lec
set lec.fk_socpeople = 64
where 
-- Try to avoid the error by non including the values that are the same
(select count(*)
 from llx_element_contact ec
 where ec.fk_socpeople = 64
   and ec.element_id = lec.element_id
   and ec.fk_c_type_contact = lec.fk_c_type_contact) = 0

Test data

rowid, datecreate, statut, element_id, fk_c_type_contact, fk_sockpeople
65,2015-08-31 18:59:18,4,65,160,30
66,2015-08-31 18:59:18,4,66,159,12
67,2015-08-31 18:59:18,4,67,160,12
15283,2016-03-23 11:47:15,4,6404,160,39
15284,2016-03-23 11:51:30,4,6404,160,58

Answer

You should check only two other members of unique constraint as you’re trying to assign the same value to the 3d member. No more then one row with the same two members must exist.

update llx_element_contact lec
set lec.fk_socpeople = 64
where 
-- Try to avoid the error by non including the values that are the same
(select count(*)
 from llx_element_contact ec
 where ec.element_id = lec.element_id
   and ec.fk_c_type_contact = lec.fk_c_type_contact) <=1

or

update llx_element_contact lec
set lec.fk_socpeople = 64
where 
-- Try to avoid the error by non including the values that are the same
 not exists (select 1
 from llx_element_contact ec
 where ec.element_id = lec.element_id
   and ec.fk_c_type_contact = lec.fk_c_type_contact
   and lec.fk_socpeople != ec.fk_socpeople)