Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.
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