MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table user (Id int, email varchar(20), phone int, status int)
insert into user values ( 1, 'ab@sk.be', 0111111,1)
insert into user values ( 2, 'ab@sk.be', null,0)
insert into user values ( 3, 'ab@sk.be', null,1)
insert into user values ( 4, 'cd@sk.be', 0222222,1)
insert into user values ( 5, 'cd@sk.be', null,1)
select * from user


Id
email
phone
status


1
ab@sk.be
111111
1

2
ab@sk.be
null
0

3
ab@sk.be
null
1

4
cd@sk.be
222222
1

5
cd@sk.be
null
1


update user t
inner join (
    select email, max(phone) phone from user where status = 1 group by email
) t1 on t1.email = t.email
set t.phone = t1.phone
where t.phone is null and t.status = 1
select * from user


Id
email
phone
status


1
ab@sk.be
111111
1

2
ab@sk.be
null
0

3
ab@sk.be
111111
1

4
cd@sk.be
222222
1

5
cd@sk.be
222222
1

Id	email	phone	status
1	ab@sk.be	111111	1
2	ab@sk.be	null	0
3	ab@sk.be	null	1
4	cd@sk.be	222222	1
5	cd@sk.be	null	1