MySQL update modifies multiple data

Source: Internet
Author: User
Tags mysql update

Typically, we use the following SQL statement to update field values:
Copy CodeThe code is as follows:
UPDATE mytable SET myfield= ' value ' WHERE other_field= ' other_value ';

But what do you do if you want to update multiple rows of data, and each field value is different for each row of records? For example, my blog has three categories (free resources, tutorial guides, window displays), the information for these categories is stored in the database table categories, and the Display order field Display_order is set, with each category occupying a single row of records. If I want to rearrange the order of these categories, for example (Tutorial Guide, window display, free resources), then you need to update the Display_order field of the corresponding row of the Categories table, which involves updating the multi-line record problem, At first you might think of a way to use loops to execute multiple UPDATE statements, like the following example of a PHP program:
Copy CodeThe code is as follows:
foreach ($display _order as $id = + $ordinal) {
$sql = "UPDATE categories SET Display_order = $ordinal WHERE id = $id";
mysql_query ($sql);
}

There is nothing wrong with this method, and the code is easy to understand, but in the loop statement executes more than once SQL query, in the system optimization, we always want to reduce the number of database queries as much as possible to reduce resource consumption, while improving the system speed.
Fortunately, there are better solutions, the following list of two common scenarios is only a little more complex SQL statement, but only one query can be executed once, the syntax is as follows:

? The first type: If--then statement combination
Copy CodeThe code 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 (All-in-a-

to get back to the example of our catalogue, we can use the following SQL statement:
Copy CodeThe code is as follows:
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 (All-in-a-

The above scheme greatly reduces the number of query operations of the database, greatly saves the system resources, but how to combine with our programming language? We still use the example of the directory just now, here is a sample PHP program:
Copy CodeThe code is as follows:
$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); Splicing SQL statements
}
$sql. = "END WHERE ID in ($ids)";
Echo $sql;
mysql_query ($sql);

in this example, a total of 8 rows of data were updated, but only one database query was executed, and the time saved by the above example would be negligible compared to the 8 update statements executed in the loop. But think about the benefits you'll find when you need to update 10,0000 or more lines of records! The only thing to note is the length of the SQL statement, the length of the string that is supported by the program's running environment, the data I am currently getting: The SQL statement can still execute smoothly in PHP 1,000,960, I queried the PHP document and did not find the maximum length of the specified string.

? The second type of insert
The insert syntax in MySQL has a condition of duplicate KEY update, which is used when it is necessary to determine whether a record is present or not, and the record is updated if there is no insert.
based on the above scenario, the INSERT statement is still used for updating the record, but the field is updated when the primary key is limited to repeating. as follows:
Copy CodeThe code is as follows:
INSERT into T_member (ID, name, email) VALUES
(1, ' Nick ', ' [email protected] '),
(4, ' Angel ', ' [email protected] '),
(7, ' Brank ', ' [email protected] ')
On DUPLICATE KEY UPDATE name=values (name), email=values (email);

Note: on DUPLICATE KEY update is only a unique syntax for MySQL, not SQL standard syntax!
Related Article

Contact Us

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

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.