update statement MySQL changing `WHERE` condition with `AND` produced wrong results

user_16461952 0
Viewed 44

I have the following table it has more columns but they are not related:

CREATE TABLE `vicidial_lists` (
      `list_id` bigint(14) unsigned NOT NULL,
      `list_name` varchar(50) DEFAULT NULL 
                               );

Wrongly by mistake in the below query WHERE condition was replaced by AND and limit was not used:

update vicidial_lists set list_name = replace(list_name, '15.10.2021', '15.11.2021') and list_id IN (73227,73228,73229,73230,73231) ;

This query changed all list_names of table vicidial_lists to mostly 0 and some records in 1.

LIST ID LIST NAME
73227       0  
73228       0
73229       0
73231       1

The problem was fixed , I returned the correct names from backup. I tried to read about this behavior in MySQL docs or if it was listed as a bug but, I didn't find anything. I am trying to understand why this happen.

My question, more to understand why is:

Has anyone faced the same behavior , is it listed as a bug ? Any link provided with related information would be greatly appriciated.

MySQL version: 5.6.28

sql-update mysql
1 Answers

It's not a bug, but a valid statement in MySQL.

When a WHERE-clause is missing, the UPDATE is done on all the rows. The value you are setting for the list_name-column will be the result of following boolean expression:

replace(list_name, '15.10.2021', '15.11.2021') and list_id IN (73227,73228,73229,73230,73231)

becomes either:

TRUE AND TRUE => 1

or

TRUE AND FALSE => 0

The replace-function returns a string (will be evaluated as TRUE) and the IN-clause will evaluate TRUE or FALSE. TRUE and FALSE are handled as 1 and 0 in MySQL, so the list_name will receive either 0 or 1.

user_10521300
Related