Separate Columns with conditions

Vote count:

0

I have a table with some problem data. For example, the table is as follow :

ID   NAME                            JOB
---  ---------------------------     ---------------
1    Peter                           Teacher
2    John                            Programmer
3    Tom**He is a Teacher  
4    Alan**He is a Accountant

The problem is some data have been correctly inserted but some are not. Now I want to execute a SQL in order to make the table looks like below :

ID   NAME                            JOB
---  ---------------------------     ---------------
1    Peter                           Teacher
2    John                            Programmer
3    Tom                             Teacher  
4    Alan                            Accountant

I am not familiar with SQL Statement so I can just think of using the following PHP Script to fix this problem.

$sql1 = "SELECT NAME FROM MY_TABLE WHERE JOB = '' AND NAME LIKE '%He is a %'";
$res1 = mysql_query($sql1);
while($row1 = mysql_fetch_array($res1)){
    $new_data = explode("**He is a ", $row1["NAME"]);
    $sql2 = "UPDATE MY_TABLE SET NAME = '".$data[0]."', JOB = '".$data[1]."' WHERE ID = '".$data["ID"]."'";
    mysql_query($sql2);
}

Can anyone suggest a better way for me to fix this problem with one or a few SQL Statement ? Thanks

asked 46 secs ago

Trevor. W

12

Separate Columns with conditions