UPDATE Stored Procedure Tutorial

UPDATE Stored Procedure Tutorial covers the building and advantages of using a stored procedure for updating tables.

Assumptions:

  1. You have the WPDB Power Tool installed for this tutorial.
  2. You have completed the INSERT Stored Procedure basic tutorial and have some raw data to work with.

Objectives:

  1. Understand how to build a basic UPDATE stored procedure with the WPDB Power Tool.
  2. Understand how to put controls in to prevent inadvertent update of fields and avoid corrupting the data.

Let’s begin! Open up your Stored Procedure tool and click on Blank UPDATE in order to have something to start with. Delete the comment lines. What is it we want this update procedure to do?

In the client table there are three items that may be updated: Phone Number, Website and Email. In addition, we must also consider what we must do to ensure we do not update the wrong record. That being said, this is all web based programming and the most secure and manageable key in the data is the email. Use the email as the requirement to change anything in the record. The fields that can be changed are first_name, last_name, phone, email and main_website. The created_date, updated_date and notes field are therefore reference and tracking changes. For this tutorial however, we will focus on phone, email and main_website. We will require the email as the key to update the record. That leaves us with four parameters.

  1. email – to determine which record
  2. phone – to be updated if present
  3. email – to be updated if new
  4. main_website to be updated if present

Let’s give our parameters meaningful names like changeEmail, newPhone, newEmail, newWebsite. With the previous stored procedure tutorials you should have enough practice to build this without looking at the answer. Finish this part then press Control+Enter to save the procedure. Do your best not to look at the answer till after the Control+Enter.

sp_UpdateEmailClient Initial setup example Answer

CREATE PROCEDURE sp_UpdateEmailClient(IN changeEmail VARCHAR(255), IN newPhone INT(20), 
                                      IN newEmail VARCHAR(255), newWebsite VARCHAR(255))
BEGIN
    # Description:
    #	DEMO Upadate to demonstrate how to build a UPDATE stored procedure.
    # Application:
    #	None
    # Function(s):
    #	None

    DECLARE updateEmail VARCHAR(255) DEFALUT '';

    UPDATE table_name SET field_1 = exampleStr, field_2 = exampleInt, field_3 = exampleDec
    WHERE field_1 = exampleInt;
END

Now onto the next piece. We have to give special consideration to the email. The email is the primary key to the record, however, it may or may not be getting updated. Hence, the DECLARE updateEmail in the example answer above. In this solution we are going to update all three values so we will need to do a select on two of the fields to get their values prior to the update. Then treat them the same as we will need to treat the email Creating four IF THEN ELSE statements. Also, meaning we will need to DECLARE some more variables. So see if you can setup our DECLARE and SELECT parts for the email, phone, and main_website before looking at the example answer.

sp_UpdateEmailClient with DECLARE and SELECT example Answer

Subsequently, this concludes the UPDATE Stored Procedure Tutorial. Accordingly, now is a good time to either proceed to the DELETE Basics Tutorial or move on to the DELETE Stored Procedure Tutorial. Whichever you feel ready for. None of them are going anywhere soon.