MySQL updates data with cursor cursor loop (while)

Source: Internet
Author: User

#要修改的变量

DECLARE var_id VARCHAR (+) DEFAULT ';

#需要修改的数据的数量

DECLARE Var_updatecount INT;

#当前循环次数

DECLARE Var_whiletimes INT;

#定义游标

DECLARE Var_cur CURSOR for

SELECT ID from table_name;

#打开游标

OPEN var_cur;

#将游标的数据进行赋值

FETCH var_cur into var_id;

#获取循环次数

SELECT COUNT (1) from table_name;

#开始循环

Cur_while:while (var_whiletimes <= var_updatecount) do

#更新数据

UPDATE table_name SET column_name = var_id;

#下一循环

FETCH var_cur into var_id;

#结束循环

END while;

This is a very simple example, the reason for recording this part of the code is: the first time to write the storage of MySQL, when using the cursor loop to update the data, if there is a SELECT statement in the loop, and the query result is NULL, if you are using declare CONTINUE Handler way, it will jump directly out of the loop, but this way will not, although in the beginning of the cycle, but also in advance to query the number of times needed to cycle, but such a simple query statement generally has little effect on efficiency, of course, the query cursor result set of the statement itself is particularly complex, another theory; Record this experience, in the sharing of the same time, also convenient for later reference; just contact MySQL, deficiencies, welcome advice;

MySQL updates data with cursor cursor loop (while)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.