-1

I need to update special column in every table that which name start with :-

`REPORT_<"DATE PATERN">`

How actually I can do it?.

upd:

I've tried to write stored procedure, but I'm not familiar with it, so it does not work:

   DELIMITER $$

DROP PROCEDURE IF EXISTS `debug_msg`$$

CREATE PROCEDURE debug_msg(enabled INTEGER, msg VARCHAR(255))
BEGIN
  IF enabled THEN BEGIN
    select concat("** ", msg) AS '** DEBUG:';
  END; END IF;
END $$

DELIMITER $$
DROP PROCEDURE IF EXISTS changeColumnType;
CREATE PROCEDURE changeColumnType ()
BEGIN

 DECLARE v_finished INTEGER DEFAULT 0;
        DECLARE tableName varchar(100);


 DEClARE table_cursor CURSOR FOR 

 SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%REPORT_%';

 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;

 OPEN table_cursor;

 get_tableName: LOOP

 FETCH table_cursor INTO tableName;

 IF v_finished = 1 THEN 
 LEAVE get_tableName;
 END IF;

call debug_msg(1, tableName);
ALTER TABLE tableName MODIFY COLUMN TIME VARCHAR(8);

 END LOOP get_tableName;

 CLOSE table_cursor;

END$$

DELIMITER ;

I have the following error: ERROR 1146 (42S02): Table 'test.tablename' doesn't exist.

I fails on this step ALTER TABLE tableName MODIFY COLUMN TIME VARCHAR(8);

walter
  • 309
  • 5
  • 16
  • When we use SQL, we **know** the column(s) we want to deal with upfront. If you have to *find* the special column by doing a check, your data model is absolutely and completely wrong and that's a **fact** and not my opinion. I'm sorry you're getting downvotes, and what I wrote isn't helping you but you can't use software the way it's not designed to be used. Because you used it wrong, you have a stupid way of dealing with your problem. Whatever you do at this point - it's a hack. It's ugly and it sucks and it won't be good in the long run. – N.B. Aug 04 '15 at 10:49
  • @FelixAlcala Trying to write stored proper procedure – walter Aug 04 '15 at 10:57
  • 1
    I'm not a MySQL guy, but I think this will help. In MSSQL, you'd wan to use what is called, "Dynamic SQL." In MySQL, it looks like you will need to use, "Prepared Statements." Check out [this SO question](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure). You would have a prepared statement to alter the table and pass in parameters for the changing names. Lastly, be sure to build in error handling and checks of the data to prevent unexpected results from destroying your database. – DeadZone Aug 04 '15 at 13:19
  • @N.B. - While what you said is generally true, there are times that a DBA would want to run 'dynamic' queries against the metadata. (That's why things like Dynamic SQL exist.) I don't know if this is or is not one of those times, but it seems to me that it's better to give the OP the benefit of the doubt at this point. I think your comment may have been a bit hasty and possibly a bit harsh, based on the information that we have so far. – DeadZone Aug 04 '15 at 13:24
  • @DeadZone - it's a fact that you must know a column's name in SQL, otherwise you can't query it. I'm not stating my opinion on the matter, I'm stating a fact. A data structure known as table exists for a reason, relational databases exist for a reason. I'm not really concerned what a DBA "might" want to do - it's irrelevant. You can't fry an egg with a hammer. Possible? Maybe. Is it the right use of a hammer. Obviously not. The use of the hammer known as MySQL is wrong. That's all there is to it, no discussion about it. – N.B. Aug 04 '15 at 14:55
  • @N.B. In order to avoid a conversation in the comments section, I'll leave this as my last comment. It's obvious that the OP is trying to iterate the tables and execute a query on each table meeting certain criteria. This is not uncommon in the real world & dynamic sql statements were designed for such instances. If you'd take a step back from your ironclad opinion and read the question, you'd see that. Metadata tables exist to provide this information on a dynamic basis, hammers and eggs notwithstanding. Good day to you. – DeadZone Aug 04 '15 at 16:39

1 Answers1

0

Resolved it by adding prepared statement

SET @table = tableName;

SET @s1 = CONCAT('ALTER TABLE ', @table, '  MODIFY COLUMN TIME VARCHAR(8);');
PREPARE stmt FROM @s1;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
walter
  • 309
  • 5
  • 16