A "classic" pitfall of MySQL UPDATE statement

Someone asked, for example, the following picture:

The problem can be summarized as follows: when update a record in MySQL , the syntax is correct, but the record is not updated...

When I first encountered this problem, I took this statement and executed it directly in the test library. I found that there was indeed a problem, but it was still different from the development description. Here I use test data to simulate it:

1. Problematic SQL statements

The record after execution is:

As you can see, the result is not as the developer said, "it seems to have no effect", but it actually has an effect:

why?

It seems that there is no problem with the syntax. I looked up the update syntax in the MySQL official document:

Seeing that the format of assignment_list is a comma-separated list of col_name=value , it suddenly became clear that the multi-field update statement that the developer wanted should be written like this:

Go back and try again

Sure enough, this time I got the desired result!

Summary: In an UPDATE statement, if you want to update multiple fields, you cannot use "AND" between the fields, but should separate them with commas.

Postscript: When I had some free time later, I looked back and wondered why the strange result of owner_code=0 appeared when “AND” was used to separate them. After many attempts, I found that:

is equivalent to:

And ( '43212' and owner_name='李四' ) is a logical expression, and it is not difficult to know owner_name is not '李四'. Therefore, the result of this logical expression is false, which is equivalent to 0 in MySQL !

This is the end of this article about a "classic" pitfall of MySQL UPDATE statement. For more relevant MySQL UPDATE content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!