The method of using variables to modify data in batch in mysql stored procedure

This article mainly explains "the method of using variables to modify data in batches in mysql stored procedures". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the method of using variables to modify data in batch in mysql stored procedures".

BEGIN

Number of columns in the declare I int;# lookup table

Declare column_name_temp varchar; # # column name of the lookup table

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'; www.2cto.com

Set table_temp_name = 'wy_notebill_detail2'

# # column names of lookup tables are stored in temporary tables #

Drop table if EXISTS update_column

Create temporary table update_column (an 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')

# number of columns #

SELECT count (*) into i from update_column

SELECT I; www.2cto.com

Loop1: WHILE I > 2 DO

# # assign column names to variables #

SELECT column_name into column_name_temp from update_column where a = I

SET i=i-1

# Update data in the table # #

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

At this point, I believe you have a deeper understanding of "the method of using variables to modify data in batches in mysql stored procedures". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!