>>This update is very suspicious. First of all, why you're using LEFT JOIN? You want to update ParentTable no matter what?
>
>I tried to simplify the SQL when I posted it in here. I also had to change the table names and fields. But, when I looked at it back, yes, the original SQL sent to me was using a LEFT JOIN. The original SQL contains four conditions. Three out of those four are from the LEFT JOIN table. One is from the main table. So, my guest is that is no record is found in the LEFT JOIN table, then the condition from the parent will be the only one left.
>
>But, it may well be that it should have been INNER JOIN since the debut. I have to say I didn't pay attention to that part when I received it.

If 3 out of 4 are on the other table (the one on the right side of the join), then this join is already de-facto inner join. I think you should change it to INNER JOIN first to avoid confusion.

If that join is OK and left table always have 1 (or 0) records from the child table, then

update P
set P.Field = 5 -- here P is optional (you can omit it - I always do)
from ParentTable P inner join ChildTable C on P.PK = C.FK -- still, should be 1-1 relationship
where Some P and C conditions follow