Non standard implementation code of MySQL UPDATE statement

Time:2021-10-21

Today, I’d like to introduce a difference between the update statement in MySQL database and the implementation of SQL standard (and other databases). If we don’t pay attention to this problem, it may lead to unexpected results.

Let’s first create 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 notice: This article is the original article of CSDN blogger "Tony teacher without haircut", which follows the CC 4.0 by-sa copyright agreement. For reprint, please attach the original source link and this notice.
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 col1 and col2 fields returned by the query statement?

  • For the implementation of SQL standard and other databases, the results are2and1
  • But for MySQL, the results are2and2

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

Note that this implementation of MySQL is different from the SQL standard. In addition, we also tested other databases, including Oracle, Microsoft SQL server, PostgreSQL and SQLite. Their implementation followed the SQL standard.

If we want to achieve the same effect as standard SQL in MySQL, 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;

In this way, the field col2 is updated before col1, and the old value of col1 (1) is used. The result is consistent with the SQL standard.

Conclusion: generally, when writing update statements, we don’t need to care about the update order of multiple fields. However, due to the problem of MySQL implementation, we need to pay attention to their syntax order.

This is the end of this article on the non-standard implementation of MySQL update statements. For more non-standard contents of MySQL update statements, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!