Skip to main content
Version: V1.1.0

Replace data

You can use the REPLACE INTO statement to insert or update data. This topic describes how to use the REPLACE INTO statement and provides examples.

Prepare for data replacement​

Before you replace data in a table, make sure that the following conditions are met:

  • You have connected to seekdb. For more information, see Connection methods.

  • You have the INSERT, UPDATE, and DELETE privileges on the table. For more information about how to query the privileges of the current user, see View user privileges. If you do not have the required privileges, contact the administrator to grant you the privileges. For more information about how to directly grant privileges, see Directly grant privileges.

Replace data​

Typically, you use the REPLACE INTO statement to replace one or more records in a table.

The syntax of the REPLACE INTO statement is as follows:

REPLACE INTO table_name VALUES(list_of_values);
ParameterRequiredDescriptionExample
table_nameYesThe name of the table to which you want to insert data.table1
(list_of_values)YesThe data to be inserted.(1,'CN',2001, current_timestamp ())

The REPLACE INTO statement determines whether to replace data based on the primary key or unique key of the table:

  • If no primary key or unique key conflict exists, the statement inserts a record.

  • If a primary key or unique key conflict exists, the statement deletes the existing record and inserts a new record.

    info

    We recommend that you create a primary key or unique index for the target table. Otherwise, duplicate records may be inserted.

The table has no records or has records without primary key or unique key conflicts​

If the table has no records or has records without primary key or unique key conflicts, you can use the REPLACE INTO statement to replace data, which is equivalent to using the INSERT statement to insert data.

Example:

  • After you create the t_replace table, use the REPLACE INTO statement to insert a record.

    CREATE TABLE t_replace(
        id number NOT NULL PRIMARY KEY
        , name varchar(10) NOT NULL
        , value number
        ,gmt_create timestamp NOT NULL DEFAULT current_timestamp
    );
    Query OK, 0 rows affected 

    REPLACE INTO t_replace values(1,'CN',2001, current_timestamp ());
    Query OK, 1 row affected 

    SELECT * FROM t_replace;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   |  2001 | 2025-12-23 15:04:08 |
    +----+------+-------+---------------------+
    1 row in set

    The example shows that no records were inserted into the t_replace table before the REPLACE INTO statement was executed. After the REPLACE INTO statement was executed, one record was inserted into the t_replace table.

  • Use the REPLACE INTO statement again to insert a record.

    SELECT * FROM t_replace;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   |  2001 | 2025-12-23 15:04:08 |
    +----+------+-------+---------------------+
    1 row in set

    REPLACE INTO t_replace(id, name, value, gmt_create) VALUES(2,'US',2002,current_timestamp ());
    Query OK, 1 row affected 

    SELECT * FROM t_replace;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   |  2001 | 2025-12-23 15:04:08 |
    |  2 | US   |  2002 | 2025-12-23 15:06:29 |
    +----+------+-------+---------------------+
    2 rows in set

    The example shows that one record was inserted into the t_replace table. The data (2,'US',2002,current_timestamp ()) does not violate the uniqueness constraint of the table, so the REPLACE INTO statement inserts one record into the t_replace table.

  • Use a query statement to replace the VALUES clause of the REPLACE INTO statement and insert multiple records. Insert the data from the t_insert table into the t_replace table.

    SELECT * FROM t_replace;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   |  2001 | 2025-12-23 15:04:08 |
    |  2 | US   |  2002 | 2025-12-23 15:06:29 |
    +----+------+-------+---------------------+
    2 rows in set

    SELECT * FROM t_insert;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  7 | EN   |  1007 | 2025-12-23 15:13:57 |
    |  8 | JP   |  1008 | 2025-12-23 15:14:14 |
    +----+------+-------+---------------------+
    2 rows in set

    REPLACE INTO t_replace
         SELECT id,name,value,gmt_create FROM t_insert;
    Query OK, 2 rows affected
    Records: 2  Duplicates: 0  Warnings: 0

    SELECT * FROM t_replace;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   |  2001 | 2025-12-23 15:04:08 |
    |  2 | US   |  2002 | 2025-12-23 15:06:29 |
    |  7 | EN   |  1007 | 2025-12-23 15:13:57 |
    |  8 | JP   |  1008 | 2025-12-23 15:14:14 |
    +----+------+-------+---------------------+
    4 rows in set

The table has records with primary key or unique key conflicts​

If the table has records with primary key or unique key conflicts, you can use the REPLACE INTO statement to replace the conflicting data with new data.

The following example shows how to insert a record into the t_replace table:

SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2025-12-23 15:04:08 |
|  2 | US   |  2002 | 2025-12-23 15:06:29 |
|  7 | EN   |  1007 | 2025-12-23 15:13:57 |
|  8 | JP   |  1008 | 2025-12-23 15:14:14 |
+----+------+-------+---------------------+
4 rows in set

REPLACE INTO t_replace(id, name, value, gmt_create) VALUES(2,'EN',2002,current_timestamp ());
Query OK, 2 rows affected

SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2025-12-23 15:04:08 |
|  2 | EN   |  2002 | 2025-12-23 15:15:01 |
|  7 | EN   |  1007 | 2025-12-23 15:13:57 |
|  8 | JP   |  1008 | 2025-12-23 15:14:14 |
+----+------+-------+---------------------+
4 rows in set

In this example, the id column of the t_replace table is a primary key column. The data (2,'EN',2002,current_timestamp ()) violates the uniqueness constraint of the id column. The system deletes the existing record (2,'US',2002,current_timestamp ()) and inserts the new record (2,'EN',2002,current_timestamp ()).

References​

Contents

Edit this page