20160912

When is an UPDATE actually a SELECT?

While I was working on dksfight, which was built with the mssql NodeJS package and Microsoft SQL server, I had a few places that used an UPDATE statement to modify some rows in the table. This is a short post about a snag I hit with these UPDATE statements in mssql.

The docs for mssql say how you can access the number of rows changed by an UPDATE statement. If you're using a Request object, you just read request.rowsAffected. Sure enough, this works well with a statement like this:

UPDATE fightclub_round SET
  fighterA_id = @fighterA_id,
  fighterB_id = @fighterB_id,
  outcome = @outcome
WHERE
  fightclub_id = @fcid AND
  id = @roundId
;

// and in the code
if (request.rowsAffected == 1)
{
    // success
}

But I ran into a problem with a different UPDATE statement:

UPDATE fightclub SET
    editor_password = @newEditorPassword
OUTPUT INSERTED.password_version_num
WHERE
    id = @fcid
    password_version_num = @currentPasswordVersionNum
;

This is an UPDATE statement, but when I went to access result.rowsAffected it was always 0. What gives!? Well, this statement is interesting because it's not just updating some rows; it's also returning those same rows. Soooo... is it an UPDATE or is it a SELECT?

To get the number of rows returned from a SELECT statement, you access recordSet.length, and the same holds true for these goofy "UPDECT" ("SELDATE"? "UPLECT"?) statements. Just a weird quirk of how mssql does it, I guess. What an incredibly specific blog post. Probably could have described that in one line. Oh well! I am nothing if not long-winded.

0 comments:

Post a Comment