kevinquillen
6/2/2017 - 8:56 PM

Stored procedure for MySQL - mass update *_format value tables in Drupal to a specified format. This is useful for when you change default f

Stored procedure for MySQL - mass update *_format value tables in Drupal to a specified format. This is useful for when you change default formats on a field after the field already has values, so that the user is not presented with a blank textbox on forms.

changeFieldFormatValueTo.sql
content_copyfile_download
CREATE PROCEDURE changeFieldFormatValueTo (IN format_name VARCHAR(32))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE table_name_value VARCHAR(64);
  DECLARE column_name_value VARCHAR(64);
  DECLARE cursor_fields CURSOR FOR SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'field_%_format';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
  OPEN cursor_fields;

  ColumnList: LOOP
    FETCH cursor_fields INTO table_name_value, column_name_value;
    
    IF done THEN 
      Leave ColumnList;
    END IF;
    
    SET @sql = CONCAT('UPDATE ', table_name_value, ' SET ', column_name_value, ' = "', format_name, '"');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END LOOP;

  CLOSE cursor_fields;
END;