UPDATE

Purpose

You can use this statement to modify the field values in a table.

Syntax

UPDATE [IGNORE] table_references
    SET update_asgn_list
    [WHERE where_condition]
    [ORDER BY order_list]
    [LIMIT row_count];

table_references:
    table_name [PARTITION (partition_name,...)] [, ...]

update_asgn_list:
    column_name = expr [, ...]

order_list:
    column_name [ASC|DESC] [, column_name [ASC|DESC]...]

Parameters

Considerations

Direct UPDATE operations on subqueries are not supported for either a single table or multiple tables. Otherwise, an error occurs. Here is an example:

obclient> UPDATE (SELECT * FROM T1) SET C1 = 100;
ERROR 1288 (HY000): The target table  of the UPDATE is not updatable

Examples

1. Create tables t1 and t2.

   obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
   Query OK, 0 rows affected
   obclient> SELECT * FROM t1;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  1 |    1 |
   |  2 |    2 |
   |  3 |    3 |
   |  4 |    4 |
   +----+------+
   4 rows in set
   
   obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
   Query OK, 0 rows affected
   obclient> SELECT * FROM t2;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  5 |    5 |
   |  1 |    1 |
   |  2 |    2 |
   |  3 |    3 |
   +----+------+
   4 rows in set
   

2. For the row where t1.c1 = 1 in the t1 table, set its value in the c2 column to 100.

   obclient> UPDATE t1 SET t1.c2 = 100 WHERE t1.c1 = 1;
   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   
   obclient> SELECT * FROM t1;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  1 |  100 |
   |  2 |    2 |
   |  3 |    3 |
   |  4 |    4 |
   +----+------+
   4 rows in set
   

3. For the first two rows sorted by column c2 in table t1, set their values in column c2 to 100.

   obclient> UPDATE t1 SET t1.c2 = 100 ORDER BY c2 LIMIT 2;
   Query OK, 2 rows affected
   Rows matched: 2  Changed: 2  Warnings: 0
   
   obclient> SELECT * FROM t1;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  1 |  100 |
   |  2 |  100 |
   |  3 |    3 |
   |  4 |    4 |
   +----+------+
   4 rows in set
   

4. In partition p2 of table t2, for rows where t2.c1 > 2, set their values in column c2 to 100.

   obclient> UPDATE t2 PARTITION(p2) SET t2.c2 = 100 WHERE t2.c1 > 2;
   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   
   obclient> SELECT * FROM t2;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  5 |    5 |
   |  1 |    1 |
   |  2 |    2 |
   |  3 |  100 |
   +----+------+
   4 rows in set
   

5. Update multiple tables. For rows where t1.c1 = t2.c1, set their values in column c2 of table t1 to 100 and set their values in column c2 of table t2 to 200.

   obclient> UPDATE t1,t2 SET t1.c2 = 100, t2.c2 = 200 WHERE t1.c2 = t2.c2;
   Query OK, 6 rows affected
   Rows matched: 6  Changed: 6  Warnings: 0
   
   obclient> SELECT * FROM t1;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  1 |  100 |
   |  2 |  100 |
   |  3 |  100 |
   |  4 |    4 |
   +----+------+
   4 rows in set
   
   obclient> SELECT * FROM t2;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  5 |    5 |
   |  1 |  200 |
   |  2 |  200 |
   |  3 |  200 |
   +----+------+
   4 rows in set
   

6. Update multiple tables. For rows where t1.c1 = t2.c1, set their values in column c2 in partition p2 of table t1 to 100 and set their values in column c2 in partition p2 of table t2 to 200.

   obclient> UPDATE t1,t2 PARTITION(p2) SET t1.c2 = 100, t2.c2 = 200 WHERE t1.c2 = t2.c2;
   Query OK, 6 rows affected
   Rows matched: 6  Changed: 6  Warnings: 0
   
   obclient> SELECT * FROM t1;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  1 |  100 |
   |  2 |  100 |
   |  3 |  100 |
   |  4 |    4 |
   +----+------+
   4 rows in set
   
   obclient> SELECT * FROM t2;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  5 |    5 |
   |  1 |  200 |
   |  2 |  200 |
   |  3 |  200 |
   +----+------+
   4 rows in set
   

7. Update an updatable view v.

   obclient> CREATE VIEW v AS SELECT * FROM t1;
   Query OK, 0 rows affected
   
   obclient> UPDATE v SET v.c2 = 100 WHERE v.c1 = 1;
   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   
   obclient> SELECT * FROM v;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  1 |  100 |
   |  2 |    2 |
   |  3 |    3 |
   |  4 |    4 |
   +----+------+
   4 rows in set