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 [ASCDESC] [, column_name [ASCDESC]...]

Parameters

Notes

Direct UPDATE operations on subqueries are not supported for either a single table or multiple tables. Otherwise, an error occurs. 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 (0.16 sec)
   obclient> SELECT * FROM t1;
   +----+------+
    c1  c2   
   +----+------+
     1     1 
     2     2 
     3     3 
     4     4 
   +----+------+
   4 rows in set (0.06 sec)
   
   obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
   Query OK, 0 rows affected (0.19 sec)
   obclient> SELECT * FROM t2;
   +----+------+
    c1  c2   
   +----+------+
     5     5 
     1     1 
     2     2 
     3     3 
   +----+------+
   4 rows in set (0.02 sec)
   

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

   obclient> UPDATE t1 SET t1.c2 = 100 WHERE t1.c1 = 1;
   Query OK, 1 row affected (0.02 sec)
   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 (0.01 sec)
   

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 (0.02 sec)
   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 (0.01 sec)
   

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 (0.02 sec)
   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 (0.06 sec)
   

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 (0.03 sec)
   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 (0.00 sec)
   
   obclient> SELECT * FROM t2;
   +----+------+
    c1  c2   
   +----+------+
     5     5 
     1   200 
     2   200 
     3   200 
   +----+------+
   4 rows in set (0.01 sec)
   

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 (0.02 sec)
   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 (0.01 sec)
   
   obclient> SELECT * FROM t2;
   +----+------+
    c1  c2   
   +----+------+
     5     5 
     1   200 
     2   200 
     3   200 
   +----+------+
   4 rows in set (0.01 sec)
   

7. Update an updatable view v.

   obclient> CREATE VIEW v AS SELECT * FROM t1;
   Query OK, 0 rows affected (0.07 sec)
   
   obclient> UPDATE v SET v.c2 = 100 WHERE v.c1 = 1;
   Query OK, 1 row affected (0.02 sec)
   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 (0.01 sec)