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 | 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 | 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 |