0

Let's say that I have a series of tables in mysql, and some of them are dependent on other tables (so if I didn't want to force delete, i would have to delete them in order). Now let's say I had a little script to delete them in order...Now let's say I wanted to run that in mysql workbench, and better yet have a function that took in a parameter (like userId) and did the above... How would I do such a thing in Mysql workbench in a way that I could easily retrieve and run the code (like for example if I wanted to delete a user and all the other objects associated with the user.)

bsmithers
  • 5
  • 2

1 Answers1

0

You can use a stored procedure

DELIMITER //

CREATE PROCEDURE delete_user(IN _user_id INT)
BEGIN
  START TRANSACTION;
  DELETE FROM user_data WHERE user_id = _user_id;
  -- your other delete statements go here in a proper order
  DELETE FROM users WHERE id = _user_id;
  COMMIT;
END//

DELIMITER ;

Sample usage:

CALL delete_user(2); -- delete a user with id = 2

Here is a SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • I see, so every time I wanted to run that I could just paste it into mysql workbench? So I should keep the code in a separate file on my computer? Just learning mysql and want to make sure I'm doing everything efficiently. Thanks – bsmithers Jun 01 '15 at 05:09
  • No the code of the procedure will be stored in the database itself. In order to execute it you just paste and execute `CALL delete_user();` – peterm Jun 01 '15 at 05:21
  • And how would I go about putting that file in my database? I did find this: http://stackoverflow.com/questions/5959043/how-to-insert-a-file-in-mysql-database but if you have any recomendations let me know. – bsmithers Jun 01 '15 at 05:34