0

I am trying to create a stored procedure in mySQL Workbench. The goal is to clean-up/standardize a field that reside in ad hoc tables that get loaded. Here is a snippet of the code....many many Update lines missing.

CREATE DEFINER=`root`@`localhost` PROCEDURE `Address`(TBL VARCHAR(50))
BEGIN

SET @table_name = TBL;

UPDATE table_name SET AD1 = REPLACE(AD1,"P.O. BOX ","BOX ");
UPDATE table_name SET AD1 = REPLACE(AD1,"POBOX ","BOX ");
UPDATE table_name SET AD1 = REPLACE(AD1,"P O BOX ","BOX ");

END

Issue: The function is NOT recognizing the table.

Error Code:

146 'kalo.table_name' does not exist

What am I doing wrong?

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
John K
  • 1
  • 1

2 Answers2

0
UPDATE table_name SET AD1 = REPLACE(AD1,"P.O. BOX ","BOX ");
       ^--missing @

You cannot use vars to replace SQL keywords/identifiers anyways, even if you had remembered to put the @ there. You'll need to use dynamic SQL, e.g. build the query string manually, then exec that string. There's no other way to convert your @table_name string into an actual identifier.

Marc B
  • 356,200
  • 43
  • 426
  • 500
0

The only way to do that is to put the Query in a variable and excute it as prepared statement

CREATE DEFINER=`root`@`localhost` PROCEDURE `Address`(TBL VARCHAR(50))
BEGIN

SET @table_name = TBL;

SELECT CONCAT( "UMDATE ",@table_name, " SET ....") INTO @SQL;
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- next update
-- ...
END
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
  • This makes sense but now getting syntax errors probably because of the double quotes used in the CONCAT conflicting with my replace double quotes. SELECT CONCAT("UPDATE ",@table_name," SET AD1 = REPLACE(AD1,"PO BX ","BOX ")") INTO SQL; – John K Jan 26 '16 at 22:19
  • You also can put the SET ... in single quote like **CONCAT( "UPDATE ",@table_name, ' SET AD1 = REPLACE(AD1,"P.O. BOX ","BOX ")')** – Bernd Buffen Jan 26 '16 at 22:22
  • Getting close. I did a slight modification to what you gave me to include the library. When I exclude the IN SQL I am getting a syntax error...so I excluded it and MySQL Workbench accepted it. When I run the function I get an Error 1064 You have an error in your sql syntax. check the manual for details. The GUI does show the function code and how the concat looks. When I open the version of concat as it is shown in the view...I swipe it...then paste it into sql screen it runs. Something is going on with the IN SQL; aspect. Right? – John K Jan 27 '16 at 13:14
  • I meant to say INTO SQL, not IN SQL – John K Jan 27 '16 at 13:35
  • I think I got this to work now. Give me about 30 minutes to QC – John K Jan 27 '16 at 13:55