UPDATE statements

You can use the UPDATE statement to update rows in a table. This topic describes how to use the statement.

UPDATE statement

Syntax:

UPDATE table_name
SET column_name = value [, column_name = value]...
[ WHERE condition ];

Examples

Update all records

Update all values in the value column of the t_insert table to value + 1.

obclient> UPDATE t_insert SET value=value+1 ;
Query OK, 4 rows affected
Rows matched: 4  Changed: 4  Warnings: 0

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | EN   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

When executing an UPDATE statement, ensure that the transaction is not too large. You can use the limit keyword to control the size or the where clause to control the scope. 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.

• If a transaction is too large, the following error is returned:

  obclient> UPDATE analog SET ach=ach+1;
  ERROR 4012 (HY000): Timeout
  

• Use the limit keyword to control the number of rows updated by the transaction at a time. In the following example, the ach value is updated to ach + 1 for the first 100,000 rows in the analog table.

  UPDATE analog SET ach=ach+1 LIMIT 100000;
  

• Use the where clause to update data in batches.

  UPDATE analog SET ach=ach+1 where ach=64;
  

Update some records and enable error reporting upon constraint violation

When you update identical records into a table that has a UNIQUE constraint, the database returns an error.

obclient> CREATE unique index uk_name ON t_insert(name);
Query OK, 0 rows affected

obclient> UPDATE t_insert SET name='US' where id=3;
ERROR 1062 (23000): Duplicate entry 'US' for key 'uk_name'

Other date update methods

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.

Use the INSERT ON DUPLICATE KEY UPDATE clause to avoid data conflicts

When you execute an ON DUPLICATE KEY UPDATE clause on a table, make sure the table has a PRIMARY KEY or UNIQUE constraint (index).

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

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 18:05:45 |
|  2 | US   | 10003 | 1970-01-01 18:05:54 |
|  3 | UK   | 10003 | 1970-01-01 18:05:54 |
|  4 | JP   | 10005 | 1970-01-01 18:06:08 |
+----+------+-------+---------------------+
4 rows in set