mysql set to null or 0 if right join returns no rows

Question

I have the following query which is returning no rows. however if this occurs i would like to set the values to 0 or null. how do i do this?

UPDATE `hdb`.`projects` 
RIGHT JOIN (
  SELECT jobs.PROJID, round(SUM(jobs.value),2) AS SumOfJobValues, round(Sum(jobs.Earned),2) AS SumOfEarnedValues 
  FROM jobs
  WHERE projects.PROJID = 1312184
  GROUP BY jobs.PROJID
) as temp ON projects.PROJID = temp.PROJID
SET 
  projects.VALUE = round(SumOfJobValues,2), 
  projects.WIP = round(SumOfEarnedValues,2)

I rewrote my query for using left join and it works in mysql. however when i run this using php Yii framework i does not work.

UPDATE `hdb`.`projects` 
left JOIN (
  SELECT 
    jobs.PROJID, 
    round(SUM(jobs.value),2) AS SumOfJobValues,
    round(Sum(jobs.Earned),2) AS SumOfEarnedValues 
  FROM jobs         
  GROUP BY jobs.PROJID
) as temp ON projects.PROJID = temp.PROJID
SET 
  projects.VALUE = round(SumOfJobValues,2), 
  projects.WIP = round(SumOfEarnedValues,2)
WHERE projects.PROJID = 1312184

same query running on php Yii

$sql = "UPDATE `hdb`.`projects` 
   LEFT JOIN (
     SELECT 
       jobs.PROJID, round(SUM(jobs.value),2) AS SumOfJobValues,
       round(Sum(jobs.Earned),2) AS SumOfEarnedValues 
     FROM jobs  
     WHERE jobs.PROJID = :pid                                       
     GROUP BY jobs.PROJID
     as temp ON projects.PROJID = temp.PROJID                               
     SET
       projects.VALUE = round(SumOfJobValues,2), 
       projects.WIP = round(SumOfEarnedValues,2)
     WHERE projects.PROJID = :pid";

$command=$connection->createCommand($sql);
$command->bindValue(":pid",$model->PROJID,PDO::PARAM_INT);
$command->execute();

Answer 1

You forget to close the parentheses before the 10th line on your Yii query. Also, if you don't want to join NULL values, you could use "IS NOT NULL" in your JOIN definition, something like:

UPDATE `hdb`.`projects` 
left JOIN (
  SELECT 
    jobs.PROJID, 
    round(SUM(jobs.value),2) AS SumOfJobValues,
    round(Sum(jobs.Earned),2) AS SumOfEarnedValues 
  FROM jobs         
  GROUP BY jobs.PROJID
) as temp ON (projects.PROJID = temp.PROJID) AND (temp.PROJID IS NOT NULL)
SET 
  projects.VALUE = round(SumOfJobValues,2), 
  projects.WIP = round(SumOfEarnedValues,2)
WHERE projects.PROJID = 1312184

Edit: I have not tested this, though.