What is the importance of the order of Columns in the SET clause of UPDATE statement? Will it make big difference in result set returned by MySQL?

The order of columns in the SET clause of UPDATE statement is important because MySQL provides us the updated value on columns names used in an expression. Yes, it will make big difference in the result set returned by MySQL. Following is an example to make it clear −

Example

In this example, we are having a table ‘tender’. First, we will write UPDATE statement by using ‘tender_id’ as the first and ‘rate’ as the second column in SET clause and then we will write UPDATE statement by using ‘rate’ as the first and ‘tender_id’ as the second column on table ‘tender’.

mysql> Select * from tender;

+-----------+---------+------+
| tender_id | company | rate |
+-----------+---------+------+
| 200       | ABC     | 1000 |
| 300       | ABD     | 6000 |
| 301       | ABE     | 7000 |
| 302       | ABF     | 3500 |
| 303       | ABG     | 3600 |
+-----------+---------+------+

5 rows in set (0.00 sec)

mysql> UPDATE tender SET tender_id = tender_id + 100, rate = tender_id * 4 WHERE tender_id = 200;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

The query above will update first the value of ‘tender_id’ and then update the value of ‘rate’ in accordance with the new value of ‘tender_id’. It can be observed in the result set returned by MySQL as follows −

mysql> Select * from tender;

+-----------+---------+------+
| tender_id | company | rate |
+-----------+---------+------+
| 300       | ABC     | 1200 |
| 300       | ABD     | 6000 |
| 301       | ABE     | 7000 |
| 302       | ABF     | 3500 |
| 303       | ABG     | 3600 |
+-----------+---------+------+

5 rows in set (0.00 sec)

mysql> UPDATE tender1 SET rate = tender_id * 4, tender_id = tender_id + 200 WHERE company = 'ABD';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now, the query above will update first the value of ‘rate’ in accordance with the old value of ‘tender_id’ and then update the value of ‘tender_id’ It can be observed in the result set returned by MySQL as follows −

mysql> Select * from tender;

+-----------+---------+------+
| tender_id | company | rate |
+-----------+---------+------+
| 300       | ABC     | 1200 |
| 500       | ABD     | 1200 |
| 301       | ABE     | 7000 |
| 302       | ABF     | 3500 |
| 303       | ABG     | 3600 |
+-----------+---------+------+

5 rows in set (0.00 sec)

In this way, the change of order of columns in the SET clause will make a big difference in the output.

Ankith Reddy

Published on 19-Feb-2018 12:16:32

• Related Questions & Answers
• Get the returned record set order in MySQL IN clause?
• What is the use of ORDER BY clause in MySQL?
• Get the returned record set ordered by (ORDER BY) position in MySQL 'IN' clause
• What is the meaning of ‘empty set’ in MySQL result set?
• How can we divide the result set returned by MySQL into groups?
• How can we apply filtering criteria at group levels of the result set returned by MySQL?
• How is it possible to filter out the duplications in the rows of result set return by MySQL?
• What is the significance of using multiple columns in MySQL GROUP BY clause?
• What is the query to check Character set of the columns of MySQL table?
• How can we use existing values of the rows to provide new values in the SET clause of UPDATE statement?
• What MySQL returns if sub-query, used to assign new values in the SET clause of UPDATE statement, returns no rows?
• What MySQL returns if sub-query, used to assign new values in the SET clause of UPDATE statement, returns multiple rows?
• What is the purpose of the "FOR UPDATE OF" clause in a cursor? What will happen if we fire an UPDATE statement without using this clause in a COBOL-DB2 program?
• While using the ROLLUP modifier, is it possible to use a MySQL ORDER BY clause to sort the result?
• Sort by order of values in a MySQL select statement IN clause?