MySQL - Stored procedure - union selects then update and insert

I need to create a MYSQL stored procedure which will use a UNION query to find an email address in one of five tables. Once the email address is found, the stored procedure needs to update a field in the original table, and insert the information into another table.

Here's my union:

SELECT email, 'table1' AS nameoftable, status FROM table1 
WHERE email = 'you@example.com'
UNION
SELECT email, 'table2' AS nameoftable, status FROM table2 
WHERE email =    'you@example.com'
UNION
SELECT email, 'table3' AS nameoftable, status FROM table3 
WHERE email = 'you@example.com'

This should return one record, which will give 开发者_如何学Gome the nameoftable variable that I need in the next steps eg: you@example.com table1 A

First the insert: INSERT INTO blacklist (email, tablename, added) SELECT email, 'nameoftable', added FROM nameoftable WHERE email = 'you@example.com';

Then the update: UPDATE nameoftable SET status = 'D' WHERE email = 'you@example.com';

Of course, if there is no record, the procedure should quit.

So, I am trying to roll this all into one stored procedure. This stored procedure is going to be used with an email program that will remove emails from various lists upon request. Right now, I'm doing it by hand, and it's getting tedious.

I would appreciate any pointers or ideas. Thanks in advance.

---

Your problem seems to be that you have a tablename in a variable but you need to turn the value of that variable into an identifier so that you can do normal SQL things with it. You could so something somewhat ugly like this:

IF nameoftable = 'table1' THEN
    UPDATE table1 SET status = 'D' WHERE email = 'you@example.com';
ELSEIF nameoftable = 'table2' THEN
    UPDATE table2 SET status = 'D' WHERE email = 'you@example.com';
-- etc
END IF;

Or you could use a prepared statement (which is, more or less, a form of eval):

PREPARE stmt FROM CONCAT('UPDATE ', nameoftable, ' SET status = ? WHERE email = ?');
EXECUTE stmt USING 'D', 'you@example.com';

I'm pretty sure that the CONCAT will work in that context but if not, you can build the SQL as a variable and PREPARE that:

SET @update = CONCAT('UPDATE ', nameoftable, ' SET status = ? WHERE email = ?');
PREPARE stmt FROM @update;
EXECUTE stmt USING 'D', 'you@example.com';

The rest of your procedure seems pretty straight forward once you get over the problem of converting a variable's value to an SQL identifier.