i have user table which has four columns(id , email ,phone , status ) . i trying to copy phone number from one row by email and insert it in another row with same duplicate email where status is 1. all duplicate row(by email) should have same phone numbers where status is 1 . for example :
Id | email | phone | status -------------------------------------------- 1 | ab@sk.be | 0111111 | 1 2 | ab@sk.be | | 0 3 | ab@sk.be | | 1 4 | cd@sk.be | 0222222 | 1 5 | cd@sk.be | | 1
i am trying to get below result :
Id | email | phone | status -------------------------------------------- 1 | ab@sk.be | 0111111 | 1 2 | ab@sk.be | | 0 3 | ab@sk.be | 0111111 | 1 4 | cd@sk.be | 0222222 | 1 5 | cd@sk.be | 0222222 | 1
i could find duplicate email but it’s looks impossible for me to copy phone numbers from one row to another within same emails where status is 1.
select id,email,COUNT(*) from user group by email having COUNT(*)>1
Advertisement
Answer
You could use an aggregate query to compute the maximum phone per email, and then join it with the original table in an update statement, like:
update mytable t
inner join (
select email, max(phone) phone from mytable 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
Demo on DB Fiddle provided by VBokšić (thanks).
Original data:
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
After running the update statement:
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