Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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.
189 Views
