DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `updateSql`()
BEGIN
-- 定义循环条件
DECLARE flag INT DEFAULT 0;
-- 保存表名
DECLARE tname VARCHAR(50);
-- 查询数据库test中含有userId列的表,如果区分大小写使用binary COLUMN_NAME = 'userId'
DECLARE result CURSOR FOR SELECT distinct table_name FROM information_schema.columns
WHERE column_name='factoryName' and table_name not like '%_bk'
and table_name not like '%_bak' and table_name not in ('t_baseinfo_factoryinfo' , 't_claim_saleclaimdetail', 't_claim_saleclaim', 't_claim_x5data', 't_esb_biscrapdetail', 't_esb_biscrapotherdetail', 't_esb_bisppmprojectdetail') and table_schema = 'qms';
-- 退出循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 1;
drop table if exists tempSql;
create table tempSql( tableName varchar(200), tempSql longText );
-- 打开游标
OPEN result;
WHILE flag <> 1 DO
-- 游标指向下一个位置,可以有多个数据,比如FETCH result INTO tname,ttype,...;
FETCH result INTO tname;
-- 拼接字符串表名sql,根据需要使用CONCAT函数连接
-- SET @execSql = CONCAT('SELECT * FROM ',tname,' WHERE imei = ',oldImei,' ;');
SET @execSql = CONCAT('UPDATE ', tname, ' a , t_baseinfo_factoryInfo b set a.factoryName = b.factoryName where b.deleted = 0 and a.factoryCode = b.sapFactoryCode;');
insert into tempSql(tableName, tempSql) value(tname, @execSql);
-- PREPARE stmt FROM @execSql;
-- EXECUTE stmt;
END WHILE;
END$$
DELIMITER ;