Using Variables to modify data in batches during mysql stored procedures _ MySQL
Source: Internet
Author: User
Modifying data bitsCN.com in batches using variables in mysql stored procedures
Mysql uses variables to batch modify data BEGIN declare I int; # query the table's number of columns declare column_name_temp varchar (100); # query the table's column name DECLARE column_un_change varchar (100 ); DECLARE table_temp_name varchar (100); declare description varchar (100); declare code varchar (100); set column_un_change = 'other _ INCOME '; set description = 'description '; set code = 'code'; set table_temp_name = 'wy _ notebill_detail2 '; ###################### store the column names of a table to a temporary table ########## ######### drop table if EXISTS update_column; create temporary table update_column (a int not null AUTO_INCREMENT, primary key (a) TYPE = MyISAM SELECT column_name from information_schema. 'columns 'Where table_name = table_temp_name and column_name not in (column_un_change, description, code) and data_type not in ('date', 'datetime '); ####################### count columns ############## ###### SELECT count (*) into I from update_column; SELECT I; loop1: WHILE I> 2 DO ######################### assign a column name to a variable ##### ################# SELECT column_name into column_name_temp from update_column where a = I; SET I = i-1; ######################### update table data ########### ############## set @ sqls = concat ('update ', table_temp_name, 'set', column_name_temp, '= 0'); PREPARE stmt1 from @ sqls; EXECUTE stmt1; end while loop1; # SELECT * from wy_notebill_detail; END author Liu zongcai bitsCN.com
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