The UPDATE statement is used to update the rows in a table
Basic syntax for the UPDATE statement:
UPDATE table_name
SET column_name = value [, column_name = value]...
[ WHERE condition ];
Where, column_name is the column to be updated. The value following the equal sign (=) is the target value to be updated, which must conform to the column type. The WHERE clause specifies the conditions that rows must meet to be updated. The absence of the WHERE clause means that all the records in the corresponding column are updated.
Example: Updating all records
obclient> update t_insert set value=value+1 ;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
obclient> select * from t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10002 | 2020-04-03 17:18:06 |
| 2 | US | 10003 | 2020-04-03 17:18:47 |
| 3 | EN | 10004 | 2020-04-03 17:18:47 |
| 4 | JP | 10005 | 2020-04-03 17:28:21 |
+----+------+-------+---------------------+
4 rows in set (0.00 sec)
When you execute an Update statement that involves more than one hundred thousand records without using the WHERE clause, a large transaction is generated. If the transaction is too large, the update may fail. Therefore, be sure to control the size of a transaction during an update.
Example: Updating some records and enable error reporting upon a constraint violation
obclient> create unique index uk_name on t_insert(name);
Query OK, 0 rows affected (1.99 sec)
obclient> update t_insert set name='US' where id=3;
ERROR 1062 (23000): Duplicate entry 'US' for key 'uk_name'
In addition to the explicit UPDATE statement, you can use some other statements to update data. For example, when an INSERT statement fails due to a constraint conflict, you can use the ON DUPLICATE KEY UPDATE clause to convert it into an UPDATE statement to update relevant fields.
About the INSERT ON DUPLICATE KEY UPDATE clause
When you execute an ON DUPLICATE KEY UPDATE clause on a table, make sure the table has a PRIMARY KEY or UNIQUE constraint (index).
Example: Using the INSERT ON DUPLICATE KEY UPDATE clause to avoid data conflicts
obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003)
ON DUPLICATE KEY UPDATE name='UK', value=10003 ;
Query OK, 2 rows affected (0.01 sec)
obclient> select * from t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10002 | 2020-04-03 18:05:45 |
| 2 | US | 10003 | 2020-04-03 18:05:54 |
| 3 | UK | 10003 | 2020-04-03 18:05:54 |
| 4 | JP | 10005 | 2020-04-03 18:06:08 |
+----+------+-------+---------------------+
4 rows in set (0.00 sec)
About the SELECT ... FOR UPDATE statement
The SELECT ... FOR UPDATE statement enables you to lock records during a read operation to avoid simultaneous modification of the records by other DML statements. This design is also called "pessimistic locking".
Example: Using the SELECT ... FOR UPDATE statement to lock records before making a modification
obclient> select id,name,value from t_insert where id=2 for update;
+----+------+-------+
| id | name | value |
+----+------+-------+
| 2 | US | 10003 |
+----+------+-------+
1 row in set (0.01 sec)
obclient> update t_insert set value=value+100 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0