- 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
How can we use existing values of the rows to provide new values in the SET clause of UPDATE statement?
Existing values of the row can be used to provide new values in the SET clause if that row matches the WHERE clause in an UPDATE statement. Following is the example to demonstrate it.
Example
Suppose we have a table named ‘tender’ as follows −
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)
The query below will increase values in column tender_id by 100 and also update the ‘rate’ column in accordance with new ‘tender_id’.
mysql> UPDATE tender SET tender_id = tender_id + 100, rate = tender_id + 500 Where tender_id > 300; Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> Select * from tender; +-----------+----------+------+ | tender_id | company | rate | +-----------+----------+------+ | 200 | ABC | 1000 | | 300 | ABD | 5000 | | 401 | ABE | 901 | | 402 | ABF | 902 | | 403 | ABG | 903 | +-----------+----------+------+ 5 rows in set (0.00 sec)
Advertisements