SQL update statement to add a value

问题:

   public void InsertUserReputation()
{
    StringBuilder sb = new StringBuilder();
    sb.Append("UPDATE u ");
    sb.Append(" SET u.Reputation = (u.Reputation + @Reputation)");//Problem is here u.Reputation is "Null" not 0... I think i need some if statement to check if it is a null and then update it to 0 and then add..what do you think?
    sb.Append(" FROM Users u");
    sb.Append(" INNER JOIN Comments c ON c.UsersID = u.UsersID");
    sb.Append(" WHERE c.CommentsID = @CommentsID");

    using (SqlConnection conn = new SqlConnection(AllQuestionsPresented.connectionString))
    {
        SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
        cmd.Parameters.Add("@Reputation", SqlDbType.Int).Value = 5;
        cmd.Parameters.Add("@CommentsID", SqlDbType.Int).Value = commentID;  
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

I want to add a reputation to the user of 5 points for the comment he leaves in the thread..but it fails to update why?/... commentID does get a value and so the reputation paramater

回答1:

Change

SET u.Reputation = (u.Reputation + @Reputation)

into:

SET u.Reputation = COALESCE(u.Reputation,0) + @Reputation

so NULLs in Reputation field are changed into 0 before adding @Reputation.

---

Alternatively, you can keep your code if you first set all NULL values to 0 and then make the field NOT NULL using statement ALTER TABLE. Execute the following, once:

UPDATE Users
SET Reputation = 0
WHERE Reputation IS NULL ;

ALTER TABLE Users 
ALTER COLUMN Reputation NOT NULL DEFAULT 0 ;

回答2:

sb.Append(" SET Reputation = (u.Reputation + @Reputation)");

edit: I missed your original note about u.Reputation possibly being Null. Try next:

sb.Append(" SET Reputation = (isnull(u.Reputation,0) + @Reputation)");

回答3:

You could also change

SET u.Reputation = (u.Reputation + @Reputation)

to

SET u.Reputation = COALESCE(u.Reputation + @Reputation, @Reputation, 0)

but all the existing answers well-address your needs

The only benefit the above offers is that, in the event @Reputation is also NULL, it still works.