MySQL Stored Procedure to update records with certain condition?
For this, you can use the UPDATE command along with the WHERE clause in a PROCEDURE. Let us first create a table −
mysql> create table DemoTable
-> (
-> Id int,
-> FirstName varchar(20),
-> LastName varchar(20)
-> );
Query OK, 0 rows affected (0.56 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values(101,'David','Brown');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values(102,'Chris','Brown');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values(103,'John','Doe');
Query OK, 1 row affected (0.07 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+------+-----------+----------+
| Id | FirstName | LastName |
+------+-----------+----------+
| 101 | David | Brown |
| 102 | Chris | Brown |
| 103 | John | Doe |
+------+-----------+----------+
3 rows in set (0.00 sec)
Here is the query to create a stored procedure−
mysql> delimiter //
mysql> create procedure update_sp(fName varchar(20),lName varchar(20))
-> begin
-> update DemoTable
-> set FirstName=fName,
-> LastName=lName
-> where Id=101;
-> end
-> //
Query OK, 0 rows affected (0.12 sec)
mysql> delimiter ;
Now you can call a stored procedure using call command −
mysql> call update_sp('Adam','Smith');
Query OK, 1 row affected, 2 warnings (0.08 sec)Let us check the table records once again −
mysql> select *from DemoTable;
This will produce the following output −
+------+-----------+----------+
| Id | FirstName | LastName |
+------+-----------+----------+
| 101 | Adam | Smith |
| 102 | Chris | Brown |
| 103 | John | Doe |
+------+-----------+----------+
3 rows in set (0.00 sec)
Published on 17-Dec-2019 11:31:43
