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 | 01-JEN-70  |
|  2 | US   | 10003 | 01-JEN-70  |
|  3 | EN   | 10004 | 01-JEN-70  |
+----+------+-------+------------+
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;
ORA-00001: unique constraint 'US' for key 'UK_NAME' violated