Execute an SQL query and UPDATE multiple rows of records _ MySQL

Execute an SQL query and UPDATE multiple rows to record bitsCN.com

Execute an SQL query and UPDATE multiple rows of records

Generally, the following SQL statement is used to update the field value:

1

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other _ value ';

However, what if you want to update multi-row data and record different field values in each row? For example, my blog has three classification directories (free resources, Tutorial Guide, and window display). The information of these classification directories is stored in the database table categories, the display_order field is set, and each category occupies one record. If I want to re-arrange the order of these classification directories, such as changing to (Tutorial Guide, window display, and free resources), I need to update the display_order field of the corresponding row in the categories table, this involves updating multiple rows of records. at the beginning, you may want to execute multiple UPDATE statements cyclically, just like the following php program example:

Foreach ($ display_order as $ id => $ ordinal ){

$ SQL = "UPDATE categories SET display_order = $ ordinal WHERE id = $ id ";

Mysql_query ($ SQL );

}

This method has no errors and the code is easy to understand. However, more than one SQL query is executed in a loop statement. during system optimization, we always want to minimize the number of database queries to reduce resource usage and increase the system speed. Fortunately, there are better solutions, except that SQL statements are slightly more complex, but you only need to execute one query. The syntax is as follows:

UPDATE mytable

SET myfield = CASE other_field

WHEN 1 THEN 'value'

WHEN 2 THEN 'value'

WHEN 3 THEN 'value'

END

WHERE id IN (1, 2, 3)

Back to the example of the classification directory we just mentioned, we can use the following SQL statement:

UPDATE categories

SET display_order = CASE id

WHEN 1 THEN 3

WHEN 2 THEN 1

WHEN 3 THEN 2

END

WHERE id IN (1, 2, 3)

Such an SQL statement is easy to understand, that is, the CASE keyword that many programming languages use is used to determine the proper type of different branches based on the id field value, then, update the value of the display_order field. For example, if the display_order of the original record with id = 1 is changed to 3, and the display_order of the record with id = 2 is changed to 1, you only need to execute one query to update the records of multiple rows. In general, the WHERE clause is dispensable. the meaning of adding the WHERE clause is the same as that of other common SQL statements that use the WHERE clause.

To update multiple fields in a record, use the following SQL statement:

UPDATE categories

SET display_order = CASE id

WHEN 1 THEN 3

WHEN 2 THEN 4

WHEN 3 THEN 5

END,

Title = CASE id

WHEN 1 THEN 'new Title 1'

WHEN 2 THEN 'new Title 2'

WHEN 3 THEN 'new Title 3'

END

WHERE id IN (1, 2, 3)

The above solution greatly reduces the number of database queries and saves system resources. but how can we combine it with our programming language? We still use the example of the classification directory just now. The following is a php program example:

$ Display_order = array (

1 => 4,

2 => 1,

3 => 2,

4 => 3,

5 => 9,

6 => 5,

7 => 8,

8 => 9

);

$ Ids = implode (',', array_keys ($ display_order ));

$ SQL = "UPDATE categories SET display_order = CASE id ";

Foreach ($ display_order as $ id => $ ordinal ){

$ SQL. = sprintf ("WHEN % d THEN % d", $ id, $ ordinal); // concatenate an SQL statement

}

$ SQL. = "END WHERE id IN ($ ids )";

Echo $ SQL;

Mysql_query ($ SQL );

In this example, a total of eight rows of data are updated, but only one database query is executed. compared with the eight UPDATE statements executed cyclically, the time saved in the above example is negligible. But when you need to update 10, or more rows, you will find the benefits! The only problem to be aware of is the length of the SQL statement. you need to consider the length of the string supported by the program running environment. The data I currently obtain: the SQL statement length reaches 1,000,960 and can still be successfully executed in php, I have searched the php document and have not found that the maximum length of the specified string is clearly defined.

BitsCN.com