Update data

Use the UPDATE statement to modify the field values in a table.

Example:

1. Create a sample table named t1:

   obclient> CREATE TABLE t1(c1 int primary key, c2 int);
   Query OK, 0 rows affected (0.16 sec)
   obclient> INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4);
   Query OK, 4 rows affected (0.01 sec)
   obclient> SELECT * FROM t1;
   +----+------+
   | c1 | c2   |
   +----+------+
   |  1 |    1 |
   |  2 |    2 |
   |  3 |    3 |
   |  4 |    4 |
   +----+------+
   4 rows in set (0.06 sec)
   

2. Update a single table: 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. Update a single table: For the row where v.c1 = 1, run a sub query and set its value in Column c2 to 100.

   obclient> update (SELECT * FROM t1)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 t1;
   +----+------+
   | C1 | C2   |
   +----+------+
   |  1 |  100 |
   |  2 |    2 |
   |  3 |    3 |
   |  4 |    4 |
   +----+------+
   4 rows in set (0.01 sec)
   

For more information on the syntax of the UPDATE statement, see the "UPDATE" topic in SQL Reference (Oracle Mode).