MySQL batch update data, that is: Cycle select records and then update a field

The whole story:

Today, there is a problem: a data table case_folder_info want to implement a custom sorting function, in the Table a new field sort_number, because there is existing data in the table, so this sort_number field is empty, but to achieve move up, move Down, Top and other functions, need to have the default data inside, so I want to give this field has some sort of default value.

Thinking about solutions:

1, write a small program in C #, and then take out all the data, update again, this also have to create a new project, add database connection string, add database class Library reference, too cumbersome.

2, using the database to bring the command line implementation.

Finally, through my mind of a small struggle, although SQL is not very familiar but decided to try it.

The idea of SQL implementation:

Because the command line can not be used directly using the variable declare, so simply use the very present way to solve it, first build a temporary stored procedure, in the stored procedure to implement the loop I want to assign value, and then delete the stored procedure after use.

Specific SQL statements:

/* Determine if it exists and delete it if it exists */
drop procedure if exists tempprocedure_test;/* takes a stored procedure name that is absolutely not used in a system */
/* Create temporary stored procedure */
CREATE PROCEDURE Tempprocedure_test ()
BEGIN
DECLARE isDone int default 0; /* Determine if there is a record */
DECLARE intnumber int default 1;/* for self-increment variable */
Declare Tempid char (36); /* Temporary id*/for each record loop

Declare folderids cursor FOR select a.id from Case_folder_info as a;/* take out all data that needs to be cycled */
Declare continue handler for not FOUND set isDone = 1;/* if not present, set to 1, which is true*/
Open folderids; /*open*/

REPEAT/* Start looping data */
Fetch folderids into tempid;
If not IsDone then
Update Case_folder_info set sort_number=intnumber where id=tempid;
Set intnumber=intnumber+1;
End If;
Until IsDone end repeat;
Close Folderids; /*close, corresponding to the above open folderids;*/
END;
Call Tempprocedure_test ();/* Invoke this temporary stored procedure */
drop procedure Tempprocedure_test; /* To remove trash after use */

Finish it!

MySQL batch update data, that is: Cycle select records and then update a field