Next Web Designs

Here's a simple stored procedure I find myself using for MySQL over and over again.  If you use a person's email address as a database key, you may need to occasionally update their email address, and as such, you should update all references.  Here's a simple stored procedure to accomplish this:

DELIMITER $$

DROP PROCEDURE IF EXISTS changeEmail $$
CREATE PROCEDURE changeEmail (oldEmail varchar(100), newEmail varchar(100))
BEGIN
 -- First check our arguments and that the old email exists
 IF (oldEmail IS NOT NULL) AND (newEmail IS NOT NULL) AND
    (EXISTS (SELECT * FROM users WHERE email=oldEmail))
 THEN
   -- Update all references of the old email to the new one here
   UPDATE users set email=newEmail WHERE email=oldEmail LIMIT 1;
   UPDATE billing set email=newEmail WHERE email=oldEmail;
   UPDATE cards set email=newEmail WHERE email=oldEmail;
   UPDATE orders set user_email=newEmail WHERE user_email=oldEmail;
 END IF;
END $$

DELIMITER ;

Enjoy!