UPDATE

Purpose

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

Syntax

UPDATE [LOW_PRIORITY] [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

Parameters

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 two sample tables named 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 the c2 column in the t1 table, set their values in the c2 column 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 the p2 partition of the t2 table, for rows where t2.c1 > 2, set their values in the c2 column 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 the c2 column of the t1 table to 100 and set their values in the c2 column of the t2 table 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 the c2 column in the p2 partition of the t1 table to 100 and set their values in the c2 column in the p2 partition of the t2 table 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 named 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