- 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 to write a MySQL stored function that updates the values in a table?
As we know that function is best used when we want to return a result. Hence, when we will create stored functions for manipulating tables like to Insert or Update values then it would be more or less like stored procedures. In the following example, we are creating a stored function named ‘tbl_update’ which will update the values in a table named ‘student_marks’.
mysql> Select * from student_marks// +---------+------+---------+---------+---------+ | Name | Math | English | Science | History | +---------+------+---------+---------+---------+ | Raman | 95 | 89 | 85 | 81 | | Rahul | 90 | 87 | 86 | 81 | | Mohit | 90 | 85 | 86 | 81 | | Saurabh | NULL | NULL | NULL | NULL | +---------+------+---------+---------+---------+ 4 rows in set (0.00 sec) mysql> Create Function tbl_Update(S_name Varchar(50),M1 INT,M2 INT,M3 INT,M4 INT) -> RETURNS INT -> DETERMINISTIC -> BEGIN -> UPDATE student_marks SET Math = M1,English = M2, Science = M3, History =M4 WHERE Name = S_name; -> RETURN 1; -> END // Query OK, 0 rows affected (0.03 sec) mysql> Select tbl_update('Saurabh',85,69,75,82); +------------------------------------+ | tbl_update('Saurabh',85,69,75,82) | +------------------------------------+ | 1 | +------------------------------------+ 1 row in set (0.07 sec) mysql> Select * from Student_marks; +---------+------+---------+---------+---------+ | Name | Math | English | Science | History | +---------+------+---------+---------+---------+ | Raman | 95 | 89 | 85 | 81 | | Rahul | 90 | 87 | 86 | 81 | | Mohit | 90 | 85 | 86 | 81 | | Saurabh | 85 | 69 | 75 | 82 | +---------+------+---------+---------+---------+ 4 rows in set (0.00 sec)
Advertisements