Update,replace into, update, update, update, update, update, update,replace into
Mysql > update a field in a database
UPDATE mytable SET myfield = ‘value’ WHERE other_field = ‘other_value’;
Mysql > alter table select * from ‘where’;
UPDATE mytable SET myfield = ‘value’ WHERE other_field in (‘other_values’);
Note that ‘other_values’ is a comma (,) separated string, such as: 1,2,3
If you update multiple data sets with different values, many people might write:
foreach (
id => $ordinal) {
ordinal WHERE id = $id”;
mysql_query($sql);
}
That is, the cycle of updating records one by one. Update a record once, which is poor performance and can easily cause congestion.
So can a SQL statement to achieve batch update? Mysql does not provide a direct way to implement batch updates, but it can be done with a few tricks.
UPDATE mytable
SET myfield = CASE id
WHEN 1 THEN ‘value’
WHEN 2 THEN ‘value’
WHEN 3 THEN ‘value’
END
WHERE id IN (1, 2, 3)
The case When trick is used to implement batch updates.
An example is UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1, 2, 3)
Update the display_order field so that if id=1 the display_order value is 3, if id=2 the display_order value is 4, if id=3 the display_order value is 5.
I’m writing the conditional statement together.
The WHERE section here does not affect the execution of the code, but it does improve the efficiency of SQL execution. Ensure that the SQL statement only executes the number of rows that need to be modified, there are only three data updates, and the WHERE clause ensures that only three data rows are executed.
If multiple values are updated, only minor modifications are required:
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)
At this point, you have completed a mysql statement to update multiple records.
But to be used in business, need to combine with the server language, here take PHP as an example, construct mysql statement:
$display_order = array(
1 = > 4,
2 = > 1,
3 = > 2,
4 = > 3,
5 = > 9,
6 = > 5,
7 = > 8,
8 = > 9
);
Ids = implode(‘,’, arrayKeys (display_order)); ids = implode(‘,’, arrayKeys (display_order))
$sql = “UPDATE categories SET display_order = CASE id “;
foreach (
id => $ordinal) {
id, $ordinal);
}
ids)”;
echo $sql;
In this example, eight records are updated. The code is also easy to understand, did you learn
Mysql > update mysql > update mysql > update mysql > update mysql > update mysql > update mysql > update mysql > update mysql
1. Update a record in batches once, but the performance is poor
update test_tbl set dr=’2′ where id=1;
2. Replace into or insert into… on duplicate key update
replace into test_tbl (id,dr) values (1,’2′),(2,’3′),… (x,’y’);
Or use
insert into test_tbl (id,dr) values (1,’2′),(2,’3′),… (x,’y’) on duplicate key update dr=values(dr);
3. Create a temporary table, first update the temporary table, and then update the temporary table
create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values (0,’gone’), (1,’xx’),… (m,’yy’);
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
Note: This method requires the user to have create privileges for the temporary table.
Here is the result of the above method update 100000 data performance test:
A quick update
The real 0 m15. 557 s
The user 0 m1. 684 s
Sys 0 m1. The 372 s
replace into
Real m1 0. 394 s
The user 0 m0. The 060 s
Sys 0 m0. The 012 s
insert into on duplicate key update
Real m1 0. 474 s
The user 0 m0. The 052 s
Sys 0 m0. The 008 s
create temporary table and update:
The real 0 m0. 643 s
The user 0 m0. The 064 s
Sys 0 m0. The 004 s
According to the test results, the performance of replace into was better.
Replace into and insert into on duplicate key update
Replace into essentially deletes and then inserts duplicate records. If the updated fields are not correct, the missing fields are set to default values
Insert into updates only duplicate records and does not change other fields.
QUESTION: Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
SOLVE: In safe mode, to enforce safe points,update can only follow WHERE.
SET SQL_SAFE_UPDATES=0;