Updating Data in a Table

Updating a Table by Using DML Statements

In MogDB, you can update a table by running DML statements.

Procedure

There is a table named customer_t and the table structure is as follows:

CREATE TABLE customer_t
( c_customer_sk             integer,
  c_customer_id             char(5),
  c_first_name              char(6),
  c_last_name               char(8)
) ;

You can run the following DML statements to update data in the table.

Run the INSERT statement to insert data into the table.

Insert a row to the customer_t table.

INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');

Insert multiple rows to the customer_t table.

INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

For details on how to use INSERT, see Inserting Data to Tables.

Run the UPDATE statement to update data in the table. Change the value of the c_customer_id column to 0.
```
UPDATE customer_t SET c_customer_id = 0;
```
For details on how to use UPDATE, see UPDATE.
Run the DELETE statement to delete rows from the table.

You can use the WHERE clause to specify the rows whose data is to delete. If you do not specify it, all rows in the table are deleted and only the data structure is retained.
```
DELETE FROM customer_t WHERE c_last_name = 'Baker';
```
For details on how to use DELETE, see DELETE.
Run the TRUNCATE statement to delete all rows from the table.
```
TRUNCATE TABLE customer_t;
```
For details on how to use TRUNCATE, see TRUNCATE.

The DELETE statement deletes a row of data each time whereas the TRUNCATE statement deletes data by releasing the data page stored in the table. Therefore, data can be deleted more quickly by using TRUNCATE than using DELETE.

DELETE deletes table data but does not release table storage space. TRUNCATE deletes table data and releases table storage space.

Updating and Inserting Data by Using the MERGE INTO Statement

To add all or a large amount of data in a table to an existing table, you can run the MERGE INTO statement in MogDB to merge the two tables so that data can be quickly added to the existing table.

The MERGE INTO statement matches data in a source table with that in a target table based on a join condition. If data matches, UPDATE will be executed on the target table. Otherwise, INSERT will be executed. This statement is a convenient way to combine multiple operations and avoids multiple INSERT or UPDATE statements.

Prerequisites

You have the INSERT and UPDATE permissions for the target table and the SELECT permission for the source table.

Procedure

Create a source table named products and insert data.

mogdb=# CREATE TABLE products
( product_id INTEGER,
  product_name VARCHAR2(60),
  category VARCHAR2(60)
);

mogdb=# INSERT INTO products VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');

Create a target table named newproducts and insert data.

mogdb=# CREATE TABLE newproducts
( product_id INTEGER,
  product_name VARCHAR2(60),
  category VARCHAR2(60)
);

mogdb=# INSERT INTO newproducts VALUES
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');

Run the MERGE INTO statement to merge data in the source table products into the target table newproducts.

MERGE INTO newproducts np
USING products p
ON (np.product_id = p.product_id )
WHEN MATCHED THEN
 UPDATE SET np.product_name = p.product_name, np.category = p.category
WHEN NOT MATCHED THEN
 INSERT VALUES (p.product_id, p.product_name, p.category) ;

For details on parameters in the statement, see [Table 1](#Parameters in the MERGE INTO statement). For more information, see MERGE INTO.

Table 1 Parameters in the MERGE INTO statement

Parameter	Description	Example Value
INTO clause	Specifies a target table that is to be updated or has data to be inserted. A table alias is supported.	Value: newproducts np The table name is newproducts and the alias is np.
USING clause	Specifies a source table. A table alias is supported. If the target table is a replication table, the source table must also be a replication table.	Value: products p The table name is products and the alias is p.
ON clause	Specifies a join condition between a target table and a source table. Columns in the join condition cannot be updated.	Value: np.product_id = p.product_id The join condition is that the product_id column in the target table newproducts has equivalent values as the product_id column in the source table products.
WHEN MATCHED clause	Performs UPDATE if data in the source table matches that in the target table based on the condition. - Only one WHEN MATCHED clause can be specified. - The WHEN MATCHED clause can be omitted. If it is omitted, no operation will be performed on the rows that meet the condition in the ON clause. - Columns involved in the distribution key of the target table cannot be updated.	Value: WHEN MATCHED THEN UPDATE SET np.product_name = p.product_name, np.category = p.category When the condition in the ON clause is met, the values of the product_name and category columns in the target table newproducts are replaced with the values in the corresponding columns in the source table products.
WHEN NOT MATCHED clause	Performs INSERT if data in the source table does not match that in the target table based on the condition. - Only one WHEN NOT MATCHED clause can be specified. - The WHEN NOT MATCHED clause can be omitted. - An INSERT clause can contain only one VALUES. - The WHEN MATCHED and WHEN NOT MATCHED clauses can be exchanged in sequence. One of them can be omitted, but they cannot be omitted at the same time.	Value: WHEN NOT MATCHED THEN INSERT VALUES (p.product_id, p.product_name, p.category) Insert rows in the source table products that do not meet the condition in the ON clause into the target table newproducts.

Query the target table newproducts after the merge.

SELECT * FROM newproducts;

The command output is as follows:

 product_id |  product_name  | category
------------+----------------+-----------
      1501 | vivitar 35mm   | electrncs
      1502 | olympus camera | electrncs
      1666 | harry potter   | toys
      1600 | play gym       | toys
      1601 | lamaze         | toys
      1700 | wait interface | books
(6 rows)

Issue