MySQL updates all rows when the column name is only specified in the where clause

It’s been a while I didn’t work on mysql and I was suprised to see that the following statement is valid:

UPDATE table_A 
SET col_a = value
WHERE id;

It looks like MySQL updates all rows in the table. I have a good MSSQL background and I am trying to understand why this valid in MySql?

Also, I ran a query with a similar where clause on a varchar column:

SELECT distinct description
FROM table_A 
where NOT description;

I tought it will return only null or empty values. Instead, it returns lots of rows with non-null values.

Any ideas why?

Thanks,

Answer

WHERE x will match any rows for which x evaluates to a truthy value. Since BOOLEAN is actually a number type TINYINT(1), this works by converting to a number and then comparing to zero – any nonzero number is truthy. (NULL is falsy.)

If you write WHERE id = 123, then only for the row where id is 123, the expression id = 123 will evaluate to TRUE (which is the same as 1) and it will match, otherwise it will evaluate to FALSE (0).

But if you write WHERE id, the requirement is that id evaluates to a truthy value. If id is a number, only IDs 0 and NULL will be falsy.

However, in case of description, you have a string. And the string is first converted to a number. The reason you got many results there is that any string that starts with a number (that is nonzero) is matching, such as 01234hello (which converts to 1234, which is nonzero). Check what CONVERT(description, SIGNED) gives – if it is nonzero, then it matches.

_{This is why, when building AND or OR queries in code, you can avoid handling the case of zero conditions specially by starting with TRUE or 1 (in the AND case) or FALSE or 0 (in the OR case), since WHERE TRUE/WHERE 1 is valid (matches everything), as is WHERE FALSE/WHERE 0 (matches nothing). So you build a query by starting with WHERE 1 and adding to it: WHERE 1, WHERE 1 AND id = 123, WHERE 1 AND id = 123 AND type = 'xy', etc.}