Skip to content

Instantly share code, notes, and snippets.

@kevinquillen
Last active December 29, 2023 16:18
Show Gist options
  • Save kevinquillen/339cdf25b674a73442a4884506763571 to your computer and use it in GitHub Desktop.
Save kevinquillen/339cdf25b674a73442a4884506763571 to your computer and use it in GitHub Desktop.
Updated stored procedure for replacing string text values for block type fields.
CREATE PROCEDURE replaceFieldValueTo (IN v_string_from VARCHAR(255), IN v_string_to VARCHAR(255))
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 TABLE_NAME LIKE 'block_content_%' AND COLUMN_NAME LIKE 'field_%_value' AND DATA_TYPE = 'varchar';
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, ' = replace(', column_name_value, ', "', v_string_from, '", "', v_string_to ,'")');
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