How to Execute the Update Statement In the Stored Procedure In MySQL?

To execute an UPDATE statement within a stored procedure in MySQL, you need to follow the standard syntax for creating and using stored procedures in MySQL. Here is a step-by-step guide:

1. Create the Stored Procedure:

• Use the CREATE PROCEDURE statement.
• Define any parameters if needed.
• Write the UPDATE statement within the procedure.

1. Call the Stored Procedure:

• Use the CALL statement to execute the stored procedure.

Below is an example demonstrating how to create and execute a stored procedure that updates a record in a table named employees.

Step 1: Create the Table (if it doesn't exist)

First, let's create a sample table:

1
2
3
4
5
6
7
8
9

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);


-- Insert some sample data
INSERT INTO employees (name, salary) VALUES ('John Doe', 50000.00), ('Jane Smith', 60000.00);

Step 2: Create the Stored Procedure

Now, create a stored procedure to update the salary of an employee based on their id.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

DELIMITER //


CREATE PROCEDURE UpdateEmployeeSalary(
    IN emp_id INT,
    IN new_salary DECIMAL(10, 2)
)
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE id = emp_id;
END //


DELIMITER ;

Step 3: Execute the Stored Procedure

To execute the stored procedure, use the CALL statement. For example, to update the salary of the employee with id 1 to 55000.00:

1

CALL UpdateEmployeeSalary(1, 55000.00);

Full Example

Here is the complete example including table creation, stored procedure creation, and execution:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- Create the table
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);


-- Insert sample data
INSERT INTO employees (name, salary) VALUES ('John Doe', 50000.00), ('Jane Smith', 60000.00);


-- Create the stored procedure
DELIMITER //


CREATE PROCEDURE UpdateEmployeeSalary(
    IN emp_id INT,
    IN new_salary DECIMAL(10, 2)
)
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE id = emp_id;
END //


DELIMITER ;


-- Call the stored procedure
CALL UpdateEmployeeSalary(1, 55000.00);


-- Verify the update
SELECT * FROM employees;

Notes

• DELIMITER // is used to change the statement delimiter temporarily so that you can use semicolons within your procedure definition without ending the entire procedure creation statement.
• After defining the procedure, DELIMITER ; resets the delimiter to the default semicolon.
• Use IN to denote input parameters for the stored procedure.

By following these steps, you can create and execute an UPDATE statement within a stored procedure in MySQL.