如何使用单个查询更新具有相同记录的字段的记录？

Question

I have one user table with column name id,father_email,mother_email,email_notification and many more, now i want to update email_notification column with

father_email,mother_email I did it with multiple queries but i want to know that how to do it with single query only so it saves the execution time.

my code are as follows:

<?php 
    $qry="SELECT id,CONCAT(father_email,',',mother_email) as notify FROM user";
    $query=mysql_query($qry);
    while($row=mysql_fetch_assoc($query))
    {
        $qry2="UPDATE user SET email_notification='".$row['notify']."' WHERE id=".$row['id']."";
        mysql_query($qry2);
    }
?>

its working fine but i want to know how to do it with single query

Answer 1

This will update all email_notification columns for all users:

UPDATE user
SET email_notification = CONCAT_WS(',', father_email, mother_email)

(i think it's better to use CONCAT_WS that will skip null values in father_email or in mother_email)