UPDATE query changed rows without a WHERE clause but had an AND clause - why?

Question

I ran the following query, which should have had a where clause but I forgot to add it:

UPDATE
tblFormElementInstances as fei
JOIN
tblFormElements as fe using(intFormElementId)
SET
fei.intStep = 1
AND
fei.intDivisionId = 1 OR fei.intDivisionId IS NULL);

MySQL returned the following message:

--Query OK, 42 rows affected (0.06 sec)
--Rows matched: 94 Changed: 42 Warnings: 0

I would have expected it to throw a syntax error, but it didn't. Additionally there are 96 rows in that table, with differing intDivisionIds (i.e. not just 1 or NULL), which suggests that some filtering was done by MySQL (rows matched = 94).

Also, intStep was actually changed to 0, not 1.

Does anyone know:

1) Why this query worked at all?
2) Why it changed intStep to 0 and not 1?
3) Why it didn't match all 96?

(The changed count of 42 is because some rows already had intStep = 1.)

Answer 1

It works without a syntax error because 1 AND <expr> is a valid expression.

You set intStep to this expression (I have added parentheses to show precedence):

SET intStep = ((1 AND (fei.intDivisionId = 1)) OR (fei.intDivisionId IS NULL))

That's a boolean expression that is either 0 or 1, and so it changes some rows to 0 and some rows to 1. It changes to 0 if intDivisionId is not 1, and if intDivisionId is not null.

I would guess you have 96 rows in tblFormElementInstances, but only 94 of these rows have a matching row in tblFormElements. The JOIN means only matching rows are eligible for the UPDATE.

Try this query to test this theory, I bet it'll return 94:

SELECT COUNT(*) FROM tblFormElementInstances as fei
JOIN tblFormElements as fe using(intFormElementId)

---

@Jason McCreary makes a good observation that you have an imbalanced parenthesis at the end of your example. That should result in a syntax error. Since you say you didn't get a syntax error, I assume that parenthesis is included in your example by mistake.