- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- Related Articles
- Get the returned record set order in MySQL IN clause?
- 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?
- What is the use of ORDER BY clause in MySQL?
- How can we apply filtering criteria at group levels of the result set returned by MySQL?
- 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?
- How is it possible to filter out the duplications in the rows of result set return by MySQL?
- How can we use existing values of the rows to provide new values in the SET clause of UPDATE statement?
- What is the significance of using multiple columns in MySQL GROUP BY clause?
- Sort by order of values in a MySQL select statement IN clause?
- 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?
- What is the query to check Character set of the columns of MySQL table?
