Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Update Using Stored Procedures

Explore how to update records using SQL stored procedures in MySQL. This lesson guides you through creating parameterized procedures for dynamic updates, executing them, and alternative approaches like user-defined functions, prepared statements, and views. Gain practical skills for managing data with stored procedures.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- Query to update records using stored procedure
DELIMITER $$
CREATE PROCEDURE DiscountCategoryProducts(IN catID INT,
                                          IN discountPercentage DECIMAL(5, 2))
BEGIN
    UPDATE Products AS P
    SET    P.Price = P.Price * (1 - discountPercentage / 100)
    WHERE  P.CategoryID = catID;
END $$
DELIMITER ;
-- Execute stored procedure
CALL DiscountCategoryProducts(1, 10);
-- View the result
SELECT * FROM Products P WHERE P.CategoryID = 1;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The DELIMITER $$ changes the statement delimiter to $$ so semicolons can be used within the procedure.

  • Lines 4–5: The CREATE PROCEDURE defines a stored procedure called DiscountCategoryProducts with two parameters: catID and discountPercentage.

  • Lines 6–10: The BEGIN and END are used to define the body of the stored procedure. The UPDATE statement updates the existing record in the Products table with new values for prices.

  • Line 12: The DELIMITER ; resets the statement delimiter back to the default semicolon (;).

  • Line 15: The CALL statement executes the stored procedure, passing 1 and 10 as ...