0

I would like to find a more professional way to manipulate in certain columns in selected tables.

I know how to choose tables that start with a specific letter pattern:

SELECT  TABLE_NAME
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME LIKE 'prefix_mm_%'

And I know how to copy data from one column to another while changing the data type (here from UNIXTIME to TIMESTAMP):

UPDATE pages
    SET date_created = FROM_UNIXTIME(other_date_created_column)

But I have many such prefix_mm_% tables and would prefer not to do this manually for each and every one.

So I wonder: is there a way to select all the relevant tables and then manipulate the columns all at once?

Robert Wildling
  • 1,014
  • 12
  • 29
  • How do you know which column names for each table? are they always the same? or do they differ at all? Is this a one-off requirement? or regular? will the number of tables `LIKE 'prefix_mm_%'` increase or remain static? – Paul Maxwell Jun 20 '23 at 07:43
  • I'm not sure, if I understood correctly. Do you want to manipulate similar columns in different tables with certain prefixes? If yes, maybe this could be a starting point: https://stackoverflow.com/questions/193780/how-can-i-find-all-the-tables-in-mysql-with-specific-column-names-in-them (so, not finding the tables by their names but by their columns) – Conrad Neumann Jun 20 '23 at 07:48
  • Information schema to get the tables is right. `ALTER TABLE` acts on a single table only. [EXECUTE IMMEDIATE](https://mariadb.com/kb/en/execute-immediate/) to run a command generated by the information_schema query (e.g. into a user variable). – danblack Jun 20 '23 at 07:52
  • @PaulMaxwell This is a one-time thing, where a system is ported from one CMS to another and therefore columns need to be adapted. Of course I do know the column names. – Robert Wildling Jun 20 '23 at 09:00
  • @ConradNeumann Many tables have the same structure (e.g. all tables ha ve a "user_created" and a "date_created" column). But in the context of moving to another CMS the type of those columns change. The source CMS uses UNIXTIME, the goal CMS uses TIMESTAMP. So I rename columns and create new ones in all those tables that start with "rwfm". – Robert Wildling Jun 20 '23 at 09:01
  • @danblack Looks interesting. MariaDB only, though. But it seems to be a shortcut for sth that is called "prepare stmt"? Will look into that... – Robert Wildling Jun 20 '23 at 09:01
  • For a one-off (but testable) excercise I suggest writing tablenames and datetime columns names to a file, edit that file to remove unwanted rows - and enter the "other column" for each table. Check it! Load that data into a temporary table, then write a stored proc to loop through that table performing the wanted DML - this approach allows for checking result and error handling. – Paul Maxwell Jun 21 '23 at 03:16

1 Answers1

0

To me it looks like you want to:

  • Query table names and store them in a variable/cursor.
  • Loop through the cursor
  • Build and execute dynamic sql statements

The ideal language for this is PL/SQL, but it is not supported in MySQL. However, something like this could work:

DELIMITER //

CREATE PROCEDURE add_new_column()
BEGIN
  DECLARE table_name VARCHAR(100);
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR
    SELECT TABLE_NAME
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'your_schema_name'
      AND TABLE_NAME LIKE 'xyz111%';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO table_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @alter_query := CONCAT('ALTER TABLE ', table_name, ' ADD COLUMN date_column2 DATE');
    PREPARE stmt FROM @alter_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @update_query := CONCAT('UPDATE ', table_name, ' SET date_column2 = date_column');
    PREPARE stmt FROM @update_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END //

DELIMITER ;
Tobias Haider
  • 1