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();

Why not just have the value and wip have a default value of 0? — Digital Chris, Dec 18 '13 at 17:03
`project.VALUE` and `projects.WIP` already have values in there. `project` can have many `job` records. once i delete all the job records i want to update the values in `project`, would prefer to do it in 1 query if possible. — shorif2000, Dec 18 '13 at 17:06
sharif, why did you delete your (own) answer? It seems to be the right solution. — RandomSeed, Dec 27 '13 at 21:04
@RandomSeed I realized it doesn't work after thorough testing. — shorif2000, Dec 30 '13 at 10:23

score 0 · Answer 1 · answered Jan 02 '14 at 22:45

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.

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

1 Answers1