create PROCEDURE updatedate()
BEGIN
DECLARE did int ;
DECLARE stdNo VARCHAR(120);
DECLARE stdName VARCHAR(120);
DECLARE sql_update VARCHAR(500);
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 游标
DECLARE cur_account CURSOR FOR SELECT T1.DATA_ELEMENT_ID, T3.STD_NO,T3.STD_NAME_CN from adqm_data_element T1 INNER JOIN ADQM_STD_DATA_ELEMENT T2 ON T1.DATA_ELEMENT_ID = T2.DATA_ELEMENT_ID
INNER JOIN ADQM_STD T3 ON T3.STD_ID = T2.STD_ID ;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur_account;
-- 遍历
read_loop: LOOP
-- 取值 取多个字段
FETCH NEXT from cur_account INTO did,stdNo,stdName;
IF done THEN
LEAVE read_loop;
END IF;
-- 你自己想做的操作
UPDATE text set std_name=stdName,DATA_ELEMENT_NO=stdNo where DATA_ELEMENT_ID=did ;
END LOOP;
CLOSE cur_account;
END ;
call updatedate();
如果这是简单的联表更新,使用多表关联更新
列如
UPDATE library_metadata_group_by p
INNER JOIN
library_metadata a
ON p.name = a.remarks
SET p.library_category = a.library_category;