Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create a procedure to change a string in an entire database... after a lot of searching I have come up with this procedure, but it gives an error. The procedure is:

BEGIN

declare t, c varchar(100);

declare TABLES cursor for SELECT table_name, column_name
                                FROM `INFORMATION_SCHEMA`.`COLUMNS` 
                                WHERE `TABLE_SCHEMA`='MY_DATABASE';

declare exit handler for 1329 begin end;
open tables;
looping: loop 
fetch tables into t,c;
SET @s1= CONCAT('UPDATE ', 
          'MY_DATABASE', '.', t, ' SET ',
          c, ' = replace(', c,', \'', old, '\'', ' , ', '\'', new, '\')');

 PREPARE stmt FROM @s1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
end loop;
close tables;
END

The error when calling the procedure with any parameter is the well known:

1064 error in syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near 'column_to_update(c) = replace(column_to_update(c), 'old' , 'new')' at line 1

share|improve this question
2  
After the procedure crashes, run SELECT @s1; and see what you've managed to put into the variable @s1. Its value will still be set and the answer to your question should be obvious from examining it. –  Michael - sqlbot Oct 28 '13 at 18:13
    
i actualy did that before... the @s1 looks like: UPDATE MY_DB.FIRST_TABLE SET FIRST_COLUMN = replace(FIRST_COLUMN, 'old' , 'new'). i didnt use ; at the end of the command ... –  razvan mantu Oct 28 '13 at 18:30
    
Okay, is that a valid query if you execute it manually? I can't tell if those are supposed to be variable names from the procedure definition, which you have not included in your post, but you can't use program variables from procedures in prepared statements, if that's your intention. The query you generate in @s1 needs to be a valid query that can execute independent of any procedure variables. –  Michael - sqlbot Oct 28 '13 at 18:39
    
Yay... thanks for the hints guys... some back-quotes were actualy the missing thing... i'll post the updated answer... i'm a stackexchange noob so tell me how can i increase your reputation? @Michael-sqlbot –  razvan mantu Oct 28 '13 at 19:17
    
Thanks for your consideration. Stack Exchange is a Q&A site of intended for questions whose answers can be valuable to future visitors as well as to the person posting a question. When the problem was a typo, the answers are not as likely to have future value, so you might consider deleting your question. Your votes on other questions give reputation also -- browsing the site and upvoting other questions and answers that you find useful, accurate, and informative. You can click on the link of any user to see all of the Q&A that any particular user has posted, and do keyword searches. –  Michael - sqlbot Oct 28 '13 at 19:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.