MySQL Real-batch update

Mysqlthe actual combat volumeUpdate

At this stage, our business is very small, toAdmin_userin the tableRelationshipfield is updated to specifyIDis a409. Known409is a service center in the company, need to put the service center below the district, the District generationIDis a3486, the related fields areparen_id(Parent-child level relationship), you need to put409have been409all of our businesses and customers are moving to3486below, this corresponding field isRelationship, Business Introduction is complete, the following to combat operations:

View individual IDs now relationship situation

mysql> SELECT Relationship from Admin_user WHERE parent_id=409;

+-------------------------+

| Relationship |

+-------------------------+

| , 1, 30,304,405,409,437, |

| , 1, 30,304,405,409,450, |

| , 1, 30,304,405,409,464, |

| , 1, 30,304,405,409,465, |

| , 1, 30,304,405,409,471, |

| , 1, 30,304,405,409,505, |

| , 1, 30,304,405,409,540, |

| , 1, 30,304,405,409,793, |

| , 1, 30,304,405,409,794, |

| , 1,30,304,405,409,1801, |

| , 1,30,304,405,409,1802, |

| , 1,30,304,405,409,1949, |

| , 1,30,304,405,409,2015, |

+-------------------------+

Rows in Set (0.02 sec)

mysql> SELECT Relationship from Admin_user WHERE parent_id=3486;

+---------------------+

| Relationship |

+---------------------+

| , 1,30,304,3486,409, |

+---------------------+

1 row in Set (0.02 sec)

Now is to put parent_id=409 's relationship in ', 1, 30,304,405,409,437, ' all the 409 Replace 3486 ,

Stupid method: One of the update, but the business volume is small, this can be achieved, if it is tens of thousands of data, it is not advisable to do so, there is a manual change of a number is difficult to ensure correctness.

UPDATE admin_user SET relationship= ', 1,30,304,405,3486,437, ' WHERE id=437;

UPDATE admin_user SET relationship= ', 1,30,304,405,3486,450, ' WHERE id=450;

UPDATE admin_user SET relationship= ', 1,30,304,405,3486,464, ' WHERE id=464;

。。。。。。。。。。。

In fact, I am using this method to update, and then through the leadership of the new approach to implement a statement update to achieve business,

Using the connection function concat, see syntax

Mysql> Help Concat

Name: ' CONCAT '

Description:

Syntax:

CONCAT (STR1,STR2,...)

Returns the string that results from concatenating the arguments. May

There is one or more arguments. If all arguments is nonbinary strings, the

Result is a nonbinary string. If the arguments include any binary

strings, the result is a binary string. A numeric argument is converted

To its equivalent nonbinary string form.

CONCAT () returns null if any argument is null.

Url:http://dev.mysql.com/doc/refman/5.6/en/string-functions.html

Examples:

mysql> SELECT CONCAT (' My ', ' S ', ' QL ');

' MySQL '

mysql> SELECT CONCAT (' My ', NULL, ' QL ');

, NULL

Mysql> SELECT CONCAT (14.3);

' 14.3 '

I need to replace the leftmost character, which is fixed, first write the query's SQL statement and try it,

mysql> SELECT CONCAT (', 1,30,304,3486,409, ', right (relationship,length (relationship)-length (', 1, 30,304,405,409, ')) as r1,relationship from Admin_user WHERE relationship like ', 1,30,304,405,409,% ';

+--------------------------+-------------------------+

| R1 | Relationship |

+--------------------------+-------------------------+

| , 1,30,304,3486,409,437, | , 1, 30,304,405,409,437, |

| , 1,30,304,3486,409,450, | , 1, 30,304,405,409,450, |

| , 1,30,304,3486,409,464, | , 1, 30,304,405,409,464, |

| , 1,30,304,3486,409,465, | , 1, 30,304,405,409,465, |

| , 1,30,304,3486,409,471, | , 1, 30,304,405,409,471, |

| , 1,30,304,3486,409,505, | , 1, 30,304,405,409,505, |

| , 1,30,304,3486,409,540, | , 1, 30,304,405,409,540, |

| , 1,30,304,3486,409,793, | , 1, 30,304,405,409,793, |

| , 1,30,304,3486,409,794, | , 1, 30,304,405,409,794, |

| , 1,30,304,3486,409,1801, | , 1,30,304,405,409,1801, |

| , 1,30,304,3486,409,1802, | , 1,30,304,405,409,1802, |

| , 1,30,304,3486,409,1949, | , 1,30,304,405,409,1949, |

| , 1,30,304,3486,409,2015, | , 1,30,304,405,409,2015, |

+--------------------------+-------------------------+

Rows in Set (0.01 sec)

Obviously,R1 is the result I want, I can change it into an UPDATE statement,

mysql> Update Admin_user Set Relationship=concat (', 1,30,304,3486,409, ', right (relationship,length (relationship)- LENGTH (', 1,30,304,405,409, '))) where relationship like ', 1,30,304,405,409,% ';

Query OK, rows affected (0.05 sec)

Rows matched:13 changed:13 warnings:0

Mysql> Select relationship from Admin_user where parent_id=409;

+--------------------------+

| Relationship |

+--------------------------+

| , 1,30,304,3486,409,437, |

| , 1,30,304,3486,409,450, |

| , 1,30,304,3486,409,464, |

| , 1,30,304,3486,409,465, |

| , 1,30,304,3486,409,471, |

| , 1,30,304,3486,409,505, |

| , 1,30,304,3486,409,540, |

| , 1,30,304,3486,409,793, |

| , 1,30,304,3486,409,794, |

| , 1,30,304,3486,409,1801, |

| , 1,30,304,3486,409,1802, |

| , 1,30,304,3486,409,1949, |

| , 1,30,304,3486,409,2015, |

+--------------------------+

Rows in Set (0.02 sec)

Done, I tried to use when case to achieve this business, with a single update as cumbersome, this data can not see how efficient, So it is the replacement of this splicing easy to use, rookie level on the level.

MySQL Real-batch update