Non-standard implementation code for MySQL UPDATE statement

Today I will introduce to you a difference between the UPDATE statement in the MySQL database and the SQL standard (and other databases). If we don't pay attention to this problem, it may lead to unexpected results.

Let's start by creating a simple example table:

CREATE TABLE t1(
 id int, 
 col1 int, 
 col2 int
); 

INSERT INTO t1 VALUES (1, 1, 1);

SELECT * FROM t1;
id|col1|col2|
--|----|----|
 1| 1| 1|
————————————————
Copyright Statement: This article is an original article by CSDN blogger "Teacher Tony Who Doesn't Cut Hair", and follows the CC 4.0 BY-SA copyright agreement. Please attach the original source link and this statement when reprinting.
Original link: https://blog.csdn.net/horses/article/details/110238573CREATE TABLE t1(
 id int, 
 col1 int, 
 col2 int
); 

INSERT INTO t1 VALUES (1, 1, 1);

SELECT * FROM t1;
id|col1|col2|
--|----|----|
 1| 1| 1|

Then, we update the data in table t1:

UPDATE t1 
SET col1 = col1 + 1,
  col2 = col1
WHERE id = 1;

SELECT col1, col2 
FROM t1;

What are the results of the col1 and col2 fields returned by the query statement?

• For the SQL standard and other database implementations, the results are 2 and 1 respectively.
• But with MySQL the results are 2 and 2 respectively!

For MySQL, if an UPDATE statement uses a previously updated column (col1) in an expression (col2 = col1), the updated value of the column (2) will be used instead of the original value (1).

Note that this implementation in MySQL differs from the SQL standard. We also tested other databases, including Oracle, Microsoft SQL Server, PostgreSQL, and SQLite, whose implementations follow the SQL standard.

If we want to achieve the same effect in MySQL as in standard SQL, we can adjust the order of the updated fields in the UPDATE statement. For example:

UPDATE t1 
SET col2 = col1,
  col1 = col1 + 1
WHERE id = 1;

This way, the column col2 is updated before col1, using the old value of col1 (1), and the result is consistent with the SQL standard.

Conclusion: Generally, when writing UPDATE statements, we do not need to worry about the update order of multiple fields. However, due to MySQL implementation issues, we need to pay attention to their grammatical order.

This is the end of this article about the non-standard implementation of MySQL UPDATE statement. For more non-standard content related to MySQL UPDATE statement, 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!