Skip to main content

Update data

After you insert data into a table, you can use the UPDATE statement or other statements to update the records in the table. This topic describes the usage and provides examples of these statements.

Prepare for data update​

Before you update data in a table, make sure that:

  • You have connected to seekdb. For more information, see Overview of connection methods.

  • You have the UPDATE privilege on the table. For information about how to view the privileges of the current user, see View user privileges. If you do not have the privilege, contact the administrator to grant the privilege. For information about how to directly grant privileges to a user, see Directly grant privileges.

Update data by using the UPDATE statement​

Typically, you use the UPDATE statement to update data in a table.

The syntax of the UPDATE statement is as follows:

UPDATE table_name
SET column_name = value [, column_name = value]...
[ WHERE condition ];
ParameterRequiredDescription
table_nameYesThe table to update data in.
column_name = value [, column_name = value]YesThe column to update. The value after the equal sign (=) is the target value.
[ WHERE condition ]NoThe condition clause specifies the conditions that the records to be updated must meet. If you omit this clause, all records in the table are updated.

Update all records​

Update all values in the value column of the t_insert table and add 1 to each value. Example:

UPDATE t_insert SET value = value+1;
Query OK, 5 rows affected (0.007 sec)
Rows matched: 5  Changed: 5  Warnings: 0

SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 2025-12-23 11:12:23 |
|  2 | US   | 10003 | 2025-12-23 11:12:24 |
|  3 | EN   | 10004 | 2025-12-23 11:12:27 |
|  4 | JP   | 10005 | 2025-12-23 11:12:45 |
+----+------+-------+---------------------+
4 rows in set

When you execute the UPDATE statement, control the transaction size. You can use the LIMIT keyword to control the number of records to be updated or the WHERE keyword to control the range of records to be updated. This is because, when you update data without conditions, if the number of records reaches hundreds of thousands or millions, a large transaction will be generated, which may cause the execution to fail.

Update part of the records​

When you update data in a table, you can update part of the records. Assume that the t_insert table contains a large amount of data. In this case, you can use the LIMIT keyword to control the number of records to be updated or the WHERE keyword to control the range of records to be updated.

Example:

  • Use the LIMIT keyword to update the values in the value column of the first 100,000 records in the t_insert table to value+1.

    UPDATE t_insert SET value = value+1 LIMIT 100000;

  • Use the WHERE keyword to update the values in the value column of all records with a value value of 10005 in the t_insert table to value+1.

     UPDATE t_insert SET value = value+1 where value = 10005;

  • Update the value in the name column of the record where id = 3 in the t_insert table to UK.

    SELECT * FROM t_insert;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   | 10003 | 2025-12-23 11:12:23 |
    |  2 | US   | 10004 | 2025-12-23 11:12:24 |
    |  3 | EN   | 10006 | 2025-12-23 11:12:27 |
    |  4 | JP   | 10006 | 2025-12-23 11:12:45 |
    +----+------+-------+---------------------+
    4 rows in set

    UPDATE t_insert SET name = 'UK' WHERE id = 3;
    Query OK, 0 rows affected 

    SELECT * FROM t_insert;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   | 10003 | 2025-12-23 11:12:23 |
    |  2 | US   | 10004 | 2025-12-23 11:12:24 |
    |  3 | UK   | 10006 | 2025-12-23 11:12:27 |
    |  4 | JP   | 10006 | 2025-12-23 11:12:45 |
    +----+------+-------+---------------------+
    4 rows in set

Update records with unique constraints​

If a table has unique constraints, updating records to the same values will result in an error and the data update will fail. Example:

SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10003 | 2025-12-23 11:12:23 |
|  2 | US   | 10004 | 2025-12-23 11:12:24 |
|  3 | UK   | 10006 | 2025-12-23 11:12:27 |
|  4 | JP   | 10006 | 2025-12-23 11:12:45 |
+----+------+-------+---------------------+
4 rows in set

CREATE UNIQUE INDEX uk_name ON t_insert(name);
Query OK, 0 rows affected 

UPDATE t_insert SET name = 'US' where id = 3;
ERROR 1062 (23000): Duplicate entry 'US' for key 'uk_name'

Update data by using other statements​

In addition to the UPDATE statement, you can use other statements to update data. For example, when you insert data by using the INSERT statement, you can use the ON DUPLICATE KEY UPDATE clause to convert the insert statement to an update statement to update the relevant fields due to constraint conflicts.

Example:

  SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   | 10003 | 2025-12-23 11:12:23 |
  |  2 | US   | 10004 | 2025-12-23 11:12:24 |
  |  3 | UK   | 10006 | 2025-12-23 11:12:27 |
  |  4 | JP   | 10006 | 2025-12-23 11:12:45 |
  +----+------+-------+---------------------+
  4 rows in set

INSERT INTO t_insert(id, name, value) VALUES (3,'EN', 10003),(5, 'CN', 10005) ON DUPLICATE KEY UPDATE name = VALUES(name);
Query OK, 1 row affected 

SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10003 | 2025-12-23 11:12:23 |
|  2 | US   | 10004 | 2025-12-23 11:12:24 |
|  3 | EN   | 10006 | 2025-12-23 11:12:27 |
|  4 | JP   | 10006 | 2025-12-23 11:12:45 |
+----+------+-------+---------------------+
4 rows in set

In the example, ON DUPLICATE KEY UPDATE name = VALUES(name) indicates that when the inserted data has duplicate primary key values in the table, the value in the name column of the conflicting record (3,'EN', 10003) is updated to the value in the name column of the inserted data.

References​

Contents

Edit this page