Created
June 2, 2017 20:56
-
-
Save kevinquillen/ade9733d0c17296d2f4bef2094f8d745 to your computer and use it in GitHub Desktop.
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment